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

Posted on 2005-04-14
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

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.
Question by:sneidig
    LVL 28

    Accepted Solution

    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:

    INTO TempTable
    FROM YourTable
    SELECT * FROM TempTable
    LVL 11

    Expert Comment

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

    Expert Comment

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

    Author Comment

    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

    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.



    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    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.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now