Post reply

Return N rows

  • January 4, 2016 at 3:41 am

    #323351

    Hi,

    I have a question, which may be is very easy foy you guys.

    My customer wants to display the results of a query in the application (in a grid table). However, only wants to show the first 10 rows. There is a button called "Next" which should retrieve the next 10 rows every time you click on it.

    I know how to write a query to get the top 10 rows, but I've no idea how to retrieve the next 10 rows.

    "Next" button should execute a new query every time you click??

  • January 4, 2016 at 3:54 am

    #1849153

    One way, if the resultset it not too large, pull the entire thing to the client and do the paging in the grid. Advantage there is that 'next' is fast as it's not doing a round trip to the DB.

    If you want to only fetch 10 rows, then the next 10, etc, look at the OFFSET ... FETCH clause

    http://sqlmag.com/blog/sql-server-2012-t-sql-glance-offsetfetch

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2016 at 7:31 am

    #1849219

    You can also take a peek at this:

    http://m.sqlmag.com/t-sql/avoid-unnecessary-lookups-when-using-rownumber-paging

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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