Selecting the n'th to the n'th + x set of rows
-
Hello,
Does anyone know how to select rows by their position in a table. I need to be able to e.g return the 2,000th - 2,200th rows as a set. I will have many concurrent clients connecting to this table so I do not want to use cursors. The table is 800,000 in length.
Any ideas people?
Thanks,
Alan
-
This may not be the best solution, but it will work:
SELECT TOP 200 col1, col2 ...
FROM table
WHERE NOT idcol IN (SELECT TOP 2000 idcol FROM table ORDER BY somecol)
ORDER BY somecol
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Thanks Chris,
It does work and it is nice and simple. However I am developing a navigation clint for a 800,000 record table so I do not think this is efficient enough.
I am basically after a non-ADO paging method, I've managed to find a complicated sproc on http://www.4guysfromrolla.com so I'll pick my way through that.
Alan
-
If you know the starting point based on the primary key, you could use SET ROWCOUNT to return back X records. For instance, consider the sample table:
CREATE TABLE TestData (
TestID int IDENTITY,
TestData char(5))
Now for the query:
SET ROWCOUNT 20
SELECT * FROM TestData
WHERE TestID > 19
It'll only pull back the first 20 rows found where TestID > 19. Will something like this work for you?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Jon has an article with a different solution posted here:
http://www.sqlservercentral.com/columnists/jwiner/returningasubsetofarecordset.asp
Be sure to check out the discussion associated with the article - some other ideas are presented there.
Andy
-
Brian: From what I've heard, using TOP is recommended over SET ROWCOUNT, mostly because the optimizer can take TOP into account. Am I wrong on that?
Andy: Nice solution, slightly better than mine of course with your use of MAX().
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
-
Yup. My goof. My brain processed ROWCOUNT because he wanted n rows.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Brian, you need to install service pack 2 for your brain, that will make it choose TOP instead of SET ROWCOUNT.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
That's what it must be. I think I'm still on SP1.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
This is similar to what was already suggested but different enough to warrant posting:
Create a table-valued user-defined function that returns the keys for the table. This UDF takes the same parms as your stored proc plus a "skip" count.
Then in your stored proc, do your same select with a TOP 200 and a NOT IN the tables.
Something like this
select top 200 * from table
where IDCol not in dbo.udfQueryParms([same parms as proc], @SkipCount as integer)
This grows increasing inefficient as you page forward.
If you have a table this large, is it stable enough that you could "pre-page" it and add a column that indicated the page number of the row? Then you could just select page by page.
You might need several indexes and page counters to supply the different ways you allow someone to sort.
But I have to think noone will ever page through 800,000 rows 200 at a time. that's 4,000 pages.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply