Get unique ids based on order by another column using group by with row_number Here is a link to the SQLfiddle. For pagination purpose, I am first executing a query to execute if's and order by's and return a list of ids, using which I query for all the data with contained lists for those ids. I am having a problem with order by in the first query, where it wont let me use order by in a nested statement(view). In short, a fiddle is provided. Using the query

SELECT req.reqId, req.RN from (
SELECT 
    (ROW_NUMBER() OVER (ORDER BY A.requisitionId asc)) as RN,
    A.reqId as reqId                            
    FROM dbo.RequisitionToPatient B
    INNER JOIN dbo.Requisition A ON A.reqId = B.reqId
    INNER JOIN dbo.Orders C on C.reqId = A.reqId
) req
GROUP BY req.reqId, req.RN


I am getting

reqId   RN
----------
12      1
12      2
14      3
11      4
11      5
10      6
10      7
9       8
9       9
9       10
9       11


What I want is 

reqId
-----
12      
14      
11          
10       
9
Try this:

select reqID FROM(
select req.reqId, req.RN from (
select 
        (ROW_NUMBER() OVER (ORDER BY A.received desc)) as RN,
        A.reqId as reqId                            
        FROM RequisitionToPatient B
        INNER JOIN Requisition A ON A.reqId = B.reqId
        INNER JOIN Orders C on C.reqId = A.reqId
) req
group by req.reqId, req.RN
order by req.RN) AS a
  GROUP BY reqID


Output:

reqID
------
12
14
11
10
9