How to select only row 5 to 19 or row 20 to 299 from a table?

I know you can, quite easily, select the TOP 15 or 20 or 300 rows of a table, but is there a way to select 16-19? or 21-299?

I need it to fill a listview only with as many elements as the listview can display at once, and when the user scrolls up or down I want to fill the listview with the next n elements.
The table I need to show consists of hundreds of thousands of rows and to return all rows would be a performance nightmare.

Can you provide a sample code of how it could be done using a stored procedure?

Thanks in advance,

Roberto.
YetiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
This can done be very easily with a temporary table or (in SQL Server 2000) a variable of type table.

Also, you may find Experts more responsive if you took the time to re-read the EE Guidelines regarding grading standards.  For the record:
 
Questions Asked 6
Last 10 Grades Given C D D B B  
Question Grading Record 5 Answers Graded / 5 Answers Received

Anthony
0
RAGAB2000Commented:
you can use cursor.
i will test it and give you a result
0
mcallarseCommented:
I would actually recommend against a cursor or temporary table for this application. While I understand the motivation, I think a one-time hit might work better performance-wise than the constant traffic that would be necessary to browse the list. Have a question about browsing potentially "hundreds of thousands of rows" in a ListView, but that's another topic. An alternative is to populate the ListView using a disconnected recordset. This recordset can be created asynchronously at the start of the application to minimize the impact to users.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

adatheladCommented:
Here's an example of the kind of thing I'd suggest:

CREATE TABLE stp_YourProcedure
     @sintStartRecord INTEGER,
     @sintEndRecord INTEGER
AS
BEGIN
     CREATE TABLE #ptblRecords
     (
     intRecordNumber INTEGER IDENTITY(1,1),
     strFieldOne VARCHAR(100)
     )

     INSERT #ptblRecords
     SELECT FieldOne
     FROM YourTable
     
     SELECT * FROM #ptblRecords
     WHERE intRecordNumber BETWEEN @sintStartRecord AND @sintEndRecord

     DROP TABLE #ptblRecords
END
0
htarlowCommented:
--This will return records 15-25 assuming that you have an identity field.

SELECT TOP 10 * FROM TABLENAME WHERE IDENTITY_FIELD_FALUE >= 15
0
Scott PletcherSenior DBACommented:
If you have some kind of key value or sequence (for example, name or id number), then you do something like the following:

--this would actually likely be application code,
--not db code, but the logic is the same

DECLARE @lastKey INT
DECLARE @numRows INT
SET @lastKey = 0  --if first time thru

SET @numRows = 20  --or whatever

Loop:
--this code would be passed as a batch to db to exec
SET ROWCOUNT @numRows
SELECT ...
FROM ...
WHERE keyCol > @lastKey
--put returned rows into list

--user uses current list, requests next page ...
--save value of last entry added to list and get more
SET @lastKey = last_key_in_list
GOTO Loop
0
gnicoliCommented:
You can use subquery like this:
--
SELECT TOP [n] k_field, field_a, field_b, ....
FROM myTable
WHERE k_field not in (
  select TOP [m] k_field
  FROM myTable
  ORDER BY k_field)
ORDER BY k_field
--

where:
[n] is the number of rows to be returned (row per page)
[m] is the ordinal position of the first row returned (0=return the first row, 1=return the 2 row, etc.).


0
Anthony PerkinsCommented:
gnicoli,

The only problem with this approach is that [n] and [m] have to be constants.  This means that in a Stored Procedure the SQL Statement would have to be written as dynamic SQL.

Anthony
0
gnicoliCommented:
acperkins,

Thanks for the comment. It Is not necessary to write dynamic SQL to solve the problem. Here is an example:

CREATE  procedure spTopN
     @Row as smallint = 5,
     @Page as smallint = 1
     
as
BEGIN
DECLARE @sSQL nvarchar(1000)
DECLARE @rc int

     IF @Page = 1  BEGIN
          SET ROWCOUNT @Row
          SELECT * FROM myTab ORDER BY k_field
     END
     ELSE BEGIN
               
          SET @Rc = @Row * (@Page-1)
     
          SET ROWCOUNT @Rc
          SELECT k_field
            INTO #TempTab
            FROM myTab
            ORDER BY k_field
     
          SET ROWCOUNT @Row
          SELECT * FROM myTab
            WHERE k_field MS NOT IN (Select k_field From #TempTab)
            ORDER BY k_field
     END
END
GO
--
  Giorgio
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
That is true.  Set ROWCOUNT (unlike TOP) will allow a variable.

Anthony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.