?
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
?
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 69

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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