SQL statement – get 1st minimum then 2nd minimum from join

sorry for terrible description but I have struggled to word this

My 2 tables are like this

tblParent..

OrderID  Customer
789      Bob

tblChild

OrderID  SortOrder  Price
789      1          20.00           
789      3          30.00

I want the output of my statement to be OrderID (From tblParent), Customer (from tblParent), Price1 (this will be 20.00 because it’s the 1st price (based on SortOrder) from tblChild), Price2 (this will be 30.00 because its the next highest SortOrder for this OrderID in tblChild

I have tried..

SELECT A.OrderID, A.Customer, B.Price, C.Price
FROM tblParent
LEFT JOIN (SELECT Price from tblChild) B on A.OrderID=B.OrderID
LEFT JOIN (SELECT Price from tblChild) C on A.OrderID=B.OrderID

I know I need to put MIN() in somewhere for the 1st price (20.00) but then how do I go about getting the price for the 2nd minimum SortOrder?

I was doing WHERE SortOrder=1 then for the next column WHERE SortOrder=2 and so on but then I realised if someone deleted a line from the childtable then SortOrder loses its sequence (so in the example, line 2 was deleted and thats why its 1 followed by 3).

Really hope someone can help please. Thank you.

Answer

You can use window functions and conditional aggregation:

SELECT p.OrderID, p.Customer,
       MAX(c.PRICE),
       MAX(CASE WHEN seqnum = 2 THEN C.Price END) as price_2
FROM tblParent p LEFT JOIN
     (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY SortOrder) as seqnum 
      FROM tblChild c
     ) c 
     ON p.OrderID = c.OrderID
GROUP BY p.OrderID, p.Customer;

Advertisement

Answer