Post reply

Selecting the nth record

  • January 31, 2006 at 8:44 pm

    #364895

    Is there a way to select the nth record in a table?  Like for example if I have 100million records and I want to see the 50millionth record, can I select the record without using SELECT TOP 50000000 and going to the bottom of the result set to view the record?

  • February 2, 2006 at 7:28 am

    #618389

    Yes, you should be able to do that, but with a table of 100 million records I'm not sure how well it'll perform. Check out the ROW_NUMBER() function. In conjunction with a common table expression (CTE) you may be able to get what you want. For example...

    WITH source_data AS

    (

    SELECT ROW_NUMBER() OVER(ORDER BY <field to order by&gt AS row_num, *

    FROM <your table name>

    )

    SELECT *

    FROM source_data

    WHERE row_num = <row number you want>

    Fill in <field to order by>, <your table name>, and <row number you want> with appropriate values for your case.

    Again, with 100 million records you should be careful with this. Check your query plan and all that, I don't expect it'll be very fast with that many rows. May be a better way to do this, but this came to mind first.

  • February 2, 2006 at 2:46 pm

    #618525

    Thanks! I'll try it.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply