?
Solved

Returning the 20th - 30th records in a query result without using a unique index

Posted on 2005-04-14
4
Medium Priority
?
262 Views
Last Modified: 2006-11-18
Hello, Thanks for looking at my question.

I'm trying to come up with a good paging solution. So far my idea is to take the total number of rows, dived that by the number of rows that will be displayed on each page, and then send the start and end places to a stored procedure so the correct rows will be returned.

If I was returning all the rows and ordering by the id column, or unique incremental index, then it would be simple. But the results, ordered the way I need them could have the index row looking like
200
345
12
24
78
ect...

I'd like to just say, return rows 10 thru 20 of that results set, and it doesn't matter what the photographer_id is.

Is there any functionality in SQL/SQL Server 2000 that will allow me to do that?

Thank you for you time.
0
Comment
Question by:sneidig
4 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13783723
I suggest creating a temporary table that will hold this result set and add a new column which is an identity column.  Then using the identity column, you can get the rows you want.

You can do it this way:

SELECT *, IDENTITY(INT) AS SortID
INTO TempTable
FROM YourTable
GO
SELECT * FROM TempTable
WHERE SortID BETWEEN 10 AND 20
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13784319
This is probably best handled on the front end by paging a recordset, especially if you have multiple users.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13784484
>>This is probably best handled on the front end by paging a recordset, <<
Not necessarily.  There is no need to return 1000's of rows to the front end to only extract 10 rows.  This is especially true if you have multiple users, just think of the network traffic.
0
 

Author Comment

by:sneidig
ID: 13786159
Thank you everone for the comments. I worked this morning and did end up going with a temp table solution in my stored procedures. Eventually I found this article that lays out the same method http://www.dotnetjunkies.com/Tutorial/EA868776-D71E-448A-BC23-B64B871F967F.dcik

One of the things I was trying to avoid was paging thru a recordset (or dataset in this case) in my code. Though it is true there is a tradeoff, either slow up the server side code, or on the database. It has to happen somewhere.

Now that I know I'll only ever have 10 rows to sort thru at a time, I'm not worried about manipulating the data using  combinations of datatables.

Thanks,
Scott

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 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