locked
Select number of records from table RRS feed

  • Question

  • I get records from the table by using "Select top(5) itemCode,ItemName,Price from Item"

    Now I wan't to take next 5 records. How can I create query to that one.

    After that I wan't to take the previous 5 records also.

    Wednesday, April 6, 2011 3:33 AM

Answers

All replies

  • Hello,

    You mean a kind of paging function? In the next version of SQL Server "Denali" it will be a new build-in function for this.

    In the actual version there is no such function, but you can use a workaround, please have a look at "Paging records Using SQL Server 2005"


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Papy Normand Wednesday, April 6, 2011 6:27 AM
    • Marked as answer by KJian_ Tuesday, April 12, 2011 6:10 AM
    Wednesday, April 6, 2011 5:07 AM
  • This is done by adding a WHERE clause to the query to make sure the "top 5" rows begin at a point greater than the previous 5 rows.

    SELECT TOP(5), x,y,z
    FROM t
    WHERE MyPK > @LastPK
    ORDER BY MyPK

     


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by KJian_ Tuesday, April 12, 2011 6:10 AM
    Wednesday, April 6, 2011 3:19 PM
  • Dear San_D,

    Thanks for your post.

    As far my knowledge there is no such functionality in SQL query for pagination. You can use the following

    Select top 10 itemCode,ItemName,Price from Item           --Returns only top 10 records
    
    
    SELECT TOP 10 PERCENT itemCode,ItemName,Price from Item  --Returns only top 10% of your records
    

     


    Thanks and Regards, Bharath S.
    • Proposed as answer by Papy Normand Sunday, April 10, 2011 8:03 PM
    Thursday, April 7, 2011 8:12 AM