locked
How to fetch 10 records with 4 select statements or procedure RRS feed

  • Question

  • User-852894958 posted

    Hi Friends,

    I have 10 records in the table, I need to get first 3 records, second 3 records, third 3 records and finally 1 records from total 10 records.

    How to get these with select statement or procedure.

    Need help!!

    Thanks,

    Farooq

    Friday, April 17, 2009 10:22 AM

Answers

  • User-1465674731 posted

    Hi,

    First of all create a temp table with one addition field rowNumber as identity

    Now select all your record from ur actual table then insert into the temp table,

    Now from temp table you can use rowNumber colum to fetch ur desired record.

     

    Select * from tempTable

    where rowNumber <=3 -- here u might have to use some calculation, so u can pass some index like 1 that mean u r calling for first time , and with this index number u can calculate ur record size that u can use in where clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 17, 2009 1:00 PM
  • User-1199946673 posted

    Creating a temp table is not necessary! 

    If your table is ordered by a unique field (for example the Primary Key), you can do this as follows:

     

    SELECT TOP 3 * FROM tablename ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 3 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 6 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 9 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
     
    ....

    If your table is not ordered by a unique field, then it is a little bit more complicated, you should include the PrimaryKey also as the last order by field. This way both the main and the sub query are ordered exactly in the same order:

     

    SELECT TOP 3 * FROM tablename ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 3 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 6 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 9 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 24, 2009 3:51 PM

All replies

  • User-1465674731 posted

    Hi,

    First of all create a temp table with one addition field rowNumber as identity

    Now select all your record from ur actual table then insert into the temp table,

    Now from temp table you can use rowNumber colum to fetch ur desired record.

     

    Select * from tempTable

    where rowNumber <=3 -- here u might have to use some calculation, so u can pass some index like 1 that mean u r calling for first time , and with this index number u can calculate ur record size that u can use in where clause

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 17, 2009 1:00 PM
  • User-821857111 posted

    Is using the GridView's inbuilt paging capability not an option?

     

    Friday, April 17, 2009 3:48 PM
  • User-1199946673 posted

    Creating a temp table is not necessary! 

    If your table is ordered by a unique field (for example the Primary Key), you can do this as follows:

     

    SELECT TOP 3 * FROM tablename ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 3 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 6 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
    SELECT TOP 3 * FROM tablename WHERE UniqueField NOT IN (SELECT TOP 9 UniqueField FROM tablename ORDER BY UniqueField) ORDER BY UniqueField
     
    ....

    If your table is not ordered by a unique field, then it is a little bit more complicated, you should include the PrimaryKey also as the last order by field. This way both the main and the sub query are ordered exactly in the same order:

     

    SELECT TOP 3 * FROM tablename ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 3 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 6 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    SELECT TOP 3 * FROM tablename WHERE PrimaryKeyField NOT IN (SELECT TOP 9 PrimaryKeyField FROM tablename ORDER BY SomeField, PrimaryKeyField) ORDER BY SomeField, PrimaryKeyField
    
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 24, 2009 3:51 PM