How to add pagination to the following SQL

Question

I've got the following SQL and I want to know how to page it:

I've got variables for @skip and @top so I can page through..

SELECT ID FROM (
  SELECT COUNT(*) AS ID, -1 AS [Weight]
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)

  UNION ALL

  SELECT i.ID, w.[Weight]
  FROM Employees i
    INNER JOIN #WeightedIDs w
    ON (i.ID = w.ID)
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
) x
ORDER BY x.[Weight] ASC

UPDATE:

I've got the following but it doesn't work at all:

DECLARE @skip INT, @top INT
SET @skip = 30
SET @top = 100

;WITH PaginatedResults AS
(
  SELECT ID, w.[Weight],
    ROW_NUMBER() OVER (ORDER BY w.[Weight] ASC) AS RowNum
  FROM Employees i 
    INNER JOIN #WeightedIDs w 
    ON (i.ID = w.ID)  
  WHERE (i.DepartmentID = 10 and i.ShiftID = 2)
)
SELECT ID FROM (
  SELECT COUNT(*) AS ID, -1 AS [Weight]
  FROM Employees i 
    INNER JOIN #WeightedIDs w 
    ON (i.IssueID = w.id)  
  WHERE FlightID > 2 and IssueID > 0

  UNION ALL

  SELECT ID, [Weight]
  FROM PaginatedResults
  WHERE RowNum >= @skip AND RowNum < @skip + @top
) x
ORDER BY x.[Weight] ASC

possible duplicate of [how to do pagination in sql server 2008](http://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008) — , Aug 02 '12 at 17:57
It's not a duplicate because I can't figure out how to apply the ROW_NUMBER() query to my above query. — Mohamed Nuur, Aug 02 '12 at 18:08

score 0 · Accepted Answer · edited May 23 '17 at 11:55

0

This link shows a nice pagination mechanism for 2008:

Equivalent of LIMIT and OFFSET for SQL Server?

In SQL 2012 there are OFFSET and FETCH keywords that make it very easy to paginate.

This question should be applicable as well:

How to do pagination in SQL Server 2008

edited May 23 '17 at 11:55

Community

1
1

answered Aug 02 '12 at 17:51

mwigdahl

16,268
7
50
64

I've looked at all those answers and they don't answer my question. Please help! – Mohamed Nuur Aug 02 '12 at 18:23
after doing a bunch of trial and error, i found that my query is full of errors and figured out how to re-write it without having the `ROW_NUMBER() OVER (ORDER BY i.Field)` syntax in a CTE, by inlining that syntax in my inner query. The other threads recommended using the CTE syntax which broke my query. I'll accept your answer since it's the only one but it didn't really help :-( – Mohamed Nuur Aug 06 '12 at 20:36
@MohamedNuur -- Sorry to hear that my answer didn't help, but I'm glad you have a solution that works for you! – mwigdahl Aug 07 '12 at 13:20

How to add pagination to the following SQL

1 Answers1

Linked