Get unique ids based on order by another column using group by with row_number

Question

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        

Answer 1

What you want is impossible. Imagine the following table:

reqID    RN
    1     1
    2     2
    1     3

You say you want to pick distinct reqID and order by RN, see the problem? RN is ambiguous for reqID=1, it can be both 1 and 3. You need to somehow choose which of RN you want to use. In this example I choose the smallest RN for each reqID:

select reqID 
from (
    select req.reqId, min(req.RN) as RN 
    from (
        select ROW_NUMBER() OVER (ORDER BY A.received desc) as RN
             , A.reqId                            
        from RequisitionToPatient B
        join Requisition A 
            on A.reqId = B.reqId
        join Orders C 
            on C.reqId = A.reqId
    ) req
    group by req.reqId
) AS a
order by RN;

That leaves us with the table:

reqID    RN
    1     1
    2     2

which can be sorted according to RN.

I think your query can be simplified to something like:

select reqID 
FROM (
    select MAX(A.received) 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
    group by A.reqId
) req
order by RN;

I.e., no need to enumerate with the row_number function, you can order directly with received.

Answer 2

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