Slicing up results in SQL

[ACS LLC]

I need to take a section of results from the dbase so that I can then split the results over many pages, for example of there are 100 results and I have 10 per page I'd show 10 pages.

The problem is that I'm not sure how to SQL a section of desired results, so if the user is on page 2, then I would need records 11 to 20, without getting them all and then using CF start and end row which is not efficient, especially if I have hundreds of results

I do have this query, but the problem is that I can't get it to function in alphabetical order, only reverse order.

<cfset numPerPage = 10>

<cfset currentPage = #url.page#>

<cfquery datasource="DATASOURCE_NAME" name="getuser">

SELECT TOP #numPerPage# user_username,user_firstname,user_lastname

FROM

(SELECT TOP #val(currentPage * numPerPage)# user_username,user_firstname,user_lastname

FROM users AS T1 ORDER BY user_lastname DESC)

AS T2 ORDER BY user_lastname asc

</cfquery>

<CFDUMP var="#getuser#">

Appreciate any assistance

Mark

[Newsgroup_User]

which database are you using? many dbs have specific sql syntax you can
use to achieve what you need, i.e. LIMIT (records, offcet) in MySQL...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[ACS LLC]

I'm using SQL Server 2005

[Newsgroup_User]

tough luck...
the closest thing in mssql2005 is something like:

SELECT ...
FROM (
SELECT *, row_number() OVER (ORDER BY ...) AS TheRank
FROM ...
) AS sq
WHERE sq.TheRank BETWEEN @Min AND @Max

more info in the BOL...

hth

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[Newsgroup_User]

on a second thought, you could probably do something like:

SELECT TOP X *
FROM sometable
WHERE somefield NOT IN (
SELECT TOP Y somefield
FROM sometable
ORDER BY somefield)
ORDER BY somefield

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[ACS LLC]

dont seem able to get this working. Any idea what needs tweaking?

I ended up with this

<CFQUERY name="GetUser" datasource="Dsource">

SELECT TOP 10 user_username
FROM users
WHERE user_username NOT IN (
SELECT TOP 10 user_username
FROM users
ORDER BY user_username DESC)
ORDER BY user_username ASC

</CFQUERY>

<CFDUMP var="#getuser#">

[Dan_Bracuk]

run the query once, as a session variable. Then use array notation to specify what rows you want to display.

[ACS LLC]

but if I do that won't it get all the records, so if there are 500 I'd put 500 records into the session, if they only click page 1-2 I've retrieved a LOT of info that I don't need..right?

[Dan_Bracuk]

quote:

---

Originally posted by: ACS LLC
but if I do that won't it get all the records, so if there are 500 I'd put 500 records into the session, if they only click page 1-2 I've retrieved a LOT of info that I don't need..right?

---

The alternative is a trip back to the database every time the user selects a different set of results. I didn't see a where clause in your original query. If you are worried about too many records, give the user an opportunity to reduce the number by filling out a form first.

[ACS LLC]

It has to be page numbers 1| 2| 3| etc.etc.. There must be a fairly simple way of extracting a slice of data somehow 😞

[Grizzly9279]

Yeah, storing the entire result set in session is not very efficient, or scalable.

ACS LLC, your thoughts on this are absolutely correct. You need to narrow down this result set in the DB itself. The term often used to describe this effort is 'pagination'. Here is an interesting article on pagination with SQL Server 2005:

http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

[ACS LLC]

strange, I checked out that URL, and tried to put their example into play, but I get an error that SQL does not support the OVER statement

Exact error --&gt;
The OVER SQL construct or statement is not supported.