Post reply

Retrieve specific number of records from a table

  • September 25, 2002 at 4:02 pm

    #158500

    Hi,

    I am a newbie to SQL programming. I am trying to retrieve specific number of records for a table. For example, I want to retrieve records starting from the 50th to 110.

    Any help will be greatly appreciated.

    thanks in advance,

    Vijju2k

  • September 25, 2002 at 7:28 pm

    #437260

    Here is an example of a way you can retrieve 10-15 without a cursor using the top and order by clauses.

    Create table top_test (id int identity, description char(1))

    insert into top_test values('a')

    insert into top_test values('b')

    insert into top_test values('c')

    insert into top_test values('d')

    insert into top_test values('e')

    insert into top_test values('f')

    insert into top_test values('g')

    insert into top_test values('h')

    insert into top_test values('i')

    insert into top_test values('j')

    insert into top_test values('k')

    insert into top_test values('l')

    insert into top_test values('m')

    insert into top_test values('n')

    insert into top_test values('o')

    insert into top_test values('p')

    insert into top_test values('q')

    insert into top_test values('r')

    insert into top_test values('s')

    select * from (select top 6 * from (select top 15 * from top_test

    order by id asc) a

    order by id desc) b

    order by id asc

    drop table top_test

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • September 25, 2002 at 7:53 pm

    #437263

    To find a proper solution you need to consider how you data set is represented. If the data has a specific order based on unique values Greg's method will work or something like this may be better.

    SELECT * FROM tblMain oq WHERE (SELECT COUNT(*) + 1 FROM tblMain iq WHERE iq.UNICOL < oq.UNICOL) BETWEEN 50 AND 110

    If however you don't want to upset the current order and the order is not logical on any column or the column involved is not unique then consider this.

    Create a temp table with all the same columns plus an INDENTITY column start 1 increment 1. Then select the the data by using a TOP 110 into the temp table. Then you just run SELECT columnsminusouridentity FROM #temptbl WHERE identitycol > 49 which will give you what you want.

    Boils down to consider what the output should look like when done.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • September 26, 2002 at 7:33 am

    #437306

    Thank you very much for your immediate help.

    Vijju2k

  • September 26, 2002 at 8:47 am

    #437311

    James, I'm wondering about your statement "..maybe better.". In what cases is your suggestion better?

    I did a little test with the table I created with my earlier example, but I created 11,000 rows in that table this time. Then I ran both your example and mine, like so:

    select * from (select top 51 * from (select top 150 * from top_test

    order by id asc) a

    order by id desc) b

    order by id asc

    SELECT * FROM top_test oq WHERE

    (SELECT COUNT(*) + 1 FROM top_test iq

    WHERE iq.id < oq.id) BETWEEN 100 AND 150

    drop table top_test

    I displayed the execution plan. I'm not really a performance guy, but looks like the plan produced by my SQL is more efficient than yours. Am I reading the execution plan incorrectly?

    Please explain. Your help will be greatly appreciated.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • September 26, 2002 at 9:32 am

    #437313

    Sorry, I do not have an example I can think of off hand to show a difference. That is why I stated 'may be' and not will be. My opinion is try all possible solutions that meet your needs and see which gives the best performance, never assume one solution is better than another just because it works.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • September 26, 2002 at 9:51 am

    #437316

    Now it make perfect sense. Your suggestion is a good one. Thank you for the answer, and the alternative method.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

Viewing 7 posts - 1 through 6 (of 6 total)

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