0

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        
5
  • How would (1,1), (2,1), (1,3) be sorted? Should it be 1,2 or 2,1?
    – Lennart
    Jul 5 '18 at 20:59
  • i need it sorted on RN but need grouped or distinct by reqID Jul 5 '18 at 21:01
  • In the fiddle you mean? Yes, in the fiddle reqId = 1 has RN = 1,3. What I want is, 1) Data is sorted first on RN (already done in sqlfiddle) 2) Return a list of unique reqId (that were ordered in 1) Jul 5 '18 at 21:08
  • Could you describe what you want to obtain? Jul 5 '18 at 22:26
  • I want to get an ordered list of primary keys when there are filters and sorts included that may or may not be from one of the (left) joined tables. Jul 6 '18 at 16:53
0

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
3
  • I am sorry i meant to ask the question for Microsoft SQL Server and I posted the fiddle for mysql. I have updated the fiddle to the question. Jul 5 '18 at 20:44
  • you have totally changed the question. What is your new desired output
    – user166213
    Jul 5 '18 at 21:22
  • The order of the final result may differ from the order in the sub-select (order by req.RN)
    – Lennart
    Jul 5 '18 at 21:25
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.