Select rows numbers x to x+y 3

[jel]

Our application displays data in groups of 15.
That way, the user can view 15 customers sorted by name, click a 'next' button, and get the next 15.
As there is lots of data involved, it is no option to buffer all data of all customers. So, I've been looking for the most efficient way to get rows in sets: Select records 30 to 45 etc.
The thing that bugs me is that this doesn't work:

SELECT TOP 15 PK FROM mytable WHERE ...
AND PK NOT IN(SELECT TOP 30 PK FROM mytable WHERE ... ORDER BY ...) ORDER BY ...

Judging by the performance, it seems that SQL-server reevalutes that 'SELECT TOP 30' for every record again, and on 100.000 records that is not very practical.

The solution therefore seems to be to create a cursor, tempory-table or recordset with primary-keys, then do a new select using these PK's.

SELECT TOP 45 PK FROM mytable WHERE ... ORDER BY ...

But I still wonder: as SQL-server has to sort and count on a whole table anyway to do the 'SELECT TOP', why can't it also return numbers 30 to 45? Did I just overlook a key-word?

 

[SemperFiDownUnda]

SELECT TOP does not have to read an entire table if proper indexes are present as it will pull the records in the expected order.

What you can do (thanks for someone that I can't remember for making my process a bit simpler) is create a temp table before you do your query. T-SQL does not have psuedo column RowNum like Oracle. But we can create our own on the fly.

Take your query and instead of executing it to a result set you execute it into a temp table. Remove all but the PK from the SELECT clause and add in an identity column

SELECT CustomerID, IDENTITY(int,1,1) as RowNum
  INTO #Cust
  FROM dbo.Customers

SELECT * 
  FROM #Cust
 WHERE RowNum BETWEEN 16 AND 30

You can use your WHERE clause in the statement that builds the temp table then you just need to do a INNER JOIN from that temp table to your actual table(s), leaving out the WHERE clause as you filtered out the unneeded records when building the table.

This is great for "Paging" data. With a simple stored proc you can set up the pageing to be very dynamic where the StoreProc takes in the Page number to grab and the number of records per page and it does it. Thus letting more dynamically control the page sizes without having to change your stored proc.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[SleepingSand]

Hi,

If the query is written in the following format then the problem can be solved.

SELECT *
FROM Table_Name AS MyAlias
WHERE (SELECT COUNT(1) FROM Table_Name WHERE Primary_Key_ID <= MyAlias.Primary_Key_ID)
BETWEEN 16 AND 20

write back on doubt. thanks.

Sandeep

 

[jel]

Thanks Wayne, that's even neater then use a TOP twice.
Sandeep: I doubt if your solution will perform very well, as it more-or-less implies a count() for each record in the table. I'll give it a try, though.

 

[jel]

Sorry Sandeep: I gave it a try, and even without realy timing: your statement takes seconds longer (on a huge table, that is) then Francis's...

 

[nigelrivett]

select top 15 * from (select top 30 * from tbl where ... order by PK) a order by PK desc


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SemperFiDownUnda]

one thing about your solution nigel

Your rows in the pages will be in reverse

inner query produces

1
2
3
4
.
.
.
29
30

outer query pickes the top 15 in decending order which means the records come out

30
29
28
27
.
.
.
16

If this is a your data won't have rows removed then you can always just add in an identity column.

To speed up my meathod you could always include a &quot;TOP nn&quot; clause in the SELECT ... INTO statement.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[jel]

Nigel: your solution is beautifull for its simplicity, but slower then Francis's.
I think that is caused by the fact that the inner query does a select TOP X *, which will take some time when X equals some ten-thousands, and the number of fields amount to 130. (Why someone put 130 fields in one table is beyond me, but I'll live with it)

Thanks for input, everyone!

 

[nigelrivett]

>> Your rows in the pages will be in reverse
That could be left to the presentation layer but if you want it from the server
select * from
(select top 15 * from (select top 30 * from tbl where ... order by PK) a order by PK desc) b
order by PK

Depends on the design but you would normally run this as something like this so that the derived table is as small as possible

select tbl.* from
(select top 15 PK from
(select top 30 PK from tbl where ... order by PK) a
order by PK desc) b
join tbl
on tbl.PK = b.PK
order by PK

You could do the same thing using a temp table and identity but doing it on the whole table seems a waste of time.
If you used a temp table you would put an index on the ID before the second select.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jel]

ow Nigel I forget to send you a very well-deserved star...
I agree that final sorting could just as well be done in the app itself.