SELECT statement ranges

[darwen]

Dear all,

I was wondering : how do you limit the rows returned by a select statement depending on the row number ?

Let's say I want a control which returns the first (i.e. 0-20) 20 rows of a select statement. It's going to have a button which'll move onto the next 20 matches (say 20-40), and then again (40-60).

How would I go about doing this ? Can you specify in SQL the row numbers for a select statement to return ?

Of couse I could do this in client code : but if I want matches 1000-1020 then I don't want the code to have to iterate through 1000 rows before reaching the one which is to be displayed.

Thanks in advance,

Darwen.

[klintan]

You do it like this:


SELECT TOP 20 ... FROM <table>
WHERE <keyColumn> NOT IN (SELECT TOP <rowsToSkip> <keyColumn> FROM
<table> ORDER BY <SortOrder>)
ORDER BY <SortOrder>


This will work for SQL Server at least.

[darwen]

Thanks for that.

Darwen.

[exterminator]

Darwen, what database are you referring to? If that is Oracle then probably rowid and rownum would help you achieve that easily. Here is a sample from - here (http://www.techinterviews.com/?p=109):
select rownum, empno, ename from emp where rowid in
(select rowid from emp where rownum < =&rangeend
minus
select rowid from emp where rownum<&rangebegin);But MS SQL Server or Sybase probably don't have this feature. If you are using ADO.Net then you might need to get all the records into a Dataset and then extract bunches from there. Let me know your comments and what you think. Regards.

[cmiskow]

klintan's way will work but another way which is generally faster is:


SELECT ... FROM (
SELECT TOP <pageSize> ...
FROM (
SELECT TOP <rowsToSkip + pageSize> ...
FROM <tableName>
ORDER BY <sortField> <sortDirection>
) a
ORDER BY <sortField> <oppositeSortDirection>
) b
ORDER BY <sortField> <sortDirection>


It first pulls the top rowsToSkip + pageSize rows, then sorts them in reverse order and takes the top pageSize rows, thus yielding all the records for the desired page. The outermost SELECT just resorts the page into the desired order.

[darwen]

Thanks all for those. As you can guess, I'm pretty new to SQL databasing. Not new to everything else of course, just to databasing.

Glad to know that the forums are here to help. MSDN is pitifully poor on databasing from what I can see.

What are the 'a' and 'b's for CMiscow ? Excuse my ignorance but I've never seen this in SQL.

Darwen.

[exterminator]

What are the 'a' and 'b's for CMiscow ? Excuse my ignorance but I've never seen this in SQL.They are probably aliases. I have used them with columns and tables but they could be used with resultsets of a Select sub-query is the first time I have seen (may be I never faced a need for them). Regards.
1. Aliases (http://www.w3schools.com/sql/sql_alias.asp)
2. Alias for sub-query resultset (http://databasejournal.com/features/mssql/article.php/3464481)

[cmiskow]

Yeah, they are aliases for the sub-queries. I'm pretty sure you need to give subqueries aliases when selecting from them but not when they are part of the where clause. Sorry, I should have given them meaningful names.

[trends]

Hello CMiskow,

I think the query that you have suggested here will not work.Because the inner query is arranging in the asc order,whereas the outer query to that is arranging in the reverse order.It will nor work.Sorry to hurt you by means of saying this .

[cmiskow]

Actually it does work. Imagine you have a table t_Letters:

letter
a
b
c
d
e

Imagine we want to sort them in ascending order and skip two rows and get the next two rows. We expect to return c, d.

Here is the innermost SELECT:


SELECT letter FROM
( SELECT TOP 2 letter FROM
( SELECT TOP 4 letter
FROM t_Letters
ORDER BY letter ASC
) a
ORDER BY letter DESC
) b
ORDER BY letter ASC


The innermost SELECT gives us:
a
b
c
d

Then the next SELECT operates on that set and gives:
d
c

Then the outermost re-sorts them:
c
d

[darwen]

I'm new to this, so bear with me but it strikes me we can do this with only 2 select statements e.g.

Letter

a
b
c
d
e
f

We need d,e

We can select top 5, in reverse order which gives us

e
d
c
b
a

Then we can select top 2, in ascending order which gives us

d
e

i.e.


SELECT TOP 2 letter FROM
(
SELECT TOP 5 FROM t_Letters
ORDER BY letter DESC
) a
ORDER BY Letter ASC


However, if we're talking about 'letter' being the primary key for the table I suppose we need an additional 'SELECT' around the outside to draw out the necessary fields.

Am I right ?

And does this work with Jet databases ? I've got no reason to believe it won't, but it's good to check.

Thanks,

Darwen.

P.S. I'm glad this has turned into such a good thread. Thanks for all the input. It's been really great help.

[mmetzger]

Not exactly...

say you have

a
b
c
d
e
f

The first part of the query that's run:


SELECT top 5 letter from t_letters ORDER BY letter DESC


Gives you

f
e
d
c
b

So running that through

SELECT top 2 letter from (SELECT top 5 letter from t_letters ORDER BY letter DESC) a ORDER BY letter ASC


gives you

b
c

In regard to JET - most standard SQL should work, but any of the cool T-SQL functions (inline case, string manipulation, etc) won't work.

[darwen]

Thanks, I understand now.

Darwen.