Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-02
10
Medium Priority
?
208 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:Yeti
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8051796
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
 

Expert Comment

by:RAGAB2000
ID: 8052383
you can use cursor.
i will test it and give you a result
0
 
LVL 9

Expert Comment

by:mcallarse
ID: 8053944
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:adathelad
ID: 8056276
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
 
LVL 3

Expert Comment

by:htarlow
ID: 8057934
--This will return records 15-25 assuming that you have an identity field.

SELECT TOP 10 * FROM TABLENAME WHERE IDENTITY_FIELD_FALUE >= 15
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 8057963
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
 

Expert Comment

by:gnicoli
ID: 8063209
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8065519
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
 

Accepted Solution

by:
gnicoli earned 800 total points
ID: 8070241
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8072351
That is true.  Set ROWCOUNT (unlike TOP) will allow a variable.

Anthony
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question