SQLTeam.com | Weblogs | Forums

Paging with specific Order in SQLServer

tsql
sql-server-2014

#1

Hi,

I have results set with 200 rows. i need to mange paging in SQL SERVER. means, that every page need to retrieve 15 rows per page (14 pages overall, 13 with full 15 rows and one page with 5 rows). but, inside the results set, i need to check if specific ID exist, if so, i need to push him to the first page(the first 15 rows) In addition, the same row must also appear first (top 1) in the first page.
All the ID's in the results set must be unique.
Please note that the number of rows are dynamic as well as the number of pages (this is just an example)

please advise,
S


#2

hello. is this a homework or real life situation?
could u please provide sample table structure and data?


#3

In your ORDER BY you can do:

ORDER BY
   CASE WHEN MyColumn = 'ShowTopValue' THEN 1 ELSE 2 END
   , OtherSortColumn1
   , OtherSortColumn2
    ...

Do you know what, I don't actually know what the best way of paging is these days. In the bad old days we used all sorts of #Temporary tables workarounds, now we use ROW_COUNT() OVER(), but there is probably an even more efficient way now?

I haven't done a Google, the answer will be there for sure, but so will all the old methods, so might be worth some debate here?