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

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.
sneidigAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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
 
Jokra_the_BarbarianCommented:
This is probably best handled on the front end by paging a recordset, especially if you have multiple users.
0
 
Anthony PerkinsCommented:
>>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
 
sneidigAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.