Ranking functions and Joins

Question

I have the following tables.

Order_det

Ord_num	item_code	Unit_sales_price
1111	1	50
1111	2	40
1111	3	30
1111	4	20
1111	5	10
2222	3	30

Pick_det

Ord_num	Shipment_num	Item_code	Qty_to_pick	Qty_picked
1111	1	1	100	100
1111	2	1	100	100
1111	3	2	100	100
2222	3	3	200	200

I want the table as follows,

Ord_num	Shipment_num	Item_code	Qty_to_pick	Qty_picked	Unit_sales_price	Total_price (Unit_sales_price*Qty_picked)
1111	3	2	100	100	40	4000
2222	3	3	200	200	30	6000

With the help of this community, I found a very similar answer i.e, Link to that answer, Similar question

    select *
from
(
  select t1.*, max(shipment_num) over (partition by ord_num) as orders_max_ship_num
  from pick_det t1
) with_max
where shipment_num = orders_max_ship_num
order by ord_num, item_code;

My question is, where do I join the Order_det table to get the Unit_sales_price value to the already retrieved max shipment_num rows from pick_det table?

score 4 · Answer 1 · answered Sep 07 '21 at 18:39

Im late to the party, as I typed everything up and made sure it ran correctly

if OBJECT_ID('tempdb..#Order_det') IS NOT NULL DROP TABLE #Order_det;
if OBJECT_ID('tempdb..#Pick_det') IS NOT NULL DROP TABLE #Pick_det;

CREATE TABLE #Order_det (Ord_num INT, item_code INT, Unit_sales_price INT);
CREATE TABLE #Pick_det(Ord_num INT, Shipment_num INT, Item_code INT, Qty_to_pick INT, Qty_picked INT);

INSERT INTO #Order_det (Ord_num, item_code, Unit_sales_price)
VALUES
(1111,  1,  50),
(1111,  2,  40),
(1111,  3,  30),
(1111,  4,  20),
(1111,  5,  10),
(2222,  3,  30)

INSERT INTO #Pick_det (Ord_num, Shipment_num, Item_code, Qty_to_pick, Qty_picked)
VALUES
(1111,  1,  1,  100,    100),
(1111,  2,  1,  100,    100),
(1111,  3,  2,  100,    100),
(2222,  3,  3,  200,    200)

SELECT
    OrderDet.Ord_num
    , PickList.Shipment_Num
    , OrderDet.Item_code
    , PickList.Qty_to_pick 
    , PickList.Qty_picked 
    , OrderDet.Unit_sales_price
    , OrderDet.Unit_sales_price * PickList.Qty_picked AS Total_price
FROM
    (
        SELECT 
            Ord_num
            , MAX(Shipment_num) OVER (PARTITION BY ord_num) AS MaxShipment_Num--
            , Shipment_num
            , Item_code
            , Qty_to_pick 
            , Qty_picked 
        FROM #Pick_det
    ) AS PickList 
    INNER JOIN  #Order_det AS OrderDet
        ON OrderDet.Ord_num = PickList.Ord_num 
        AND OrderDet.item_code = PickList.Item_code
        and PickList.Shipment_num = PickList.MaxShipment_Num

score 1 · Accepted Answer · answered Sep 07 '21 at 18:35

You can simply base a query on yours:

with s as
(
  select *
  from
  (
    select t1.*, max(shipment_num) over (partition by ord_num) as orders_max_ship_num
    from pick_det t1
  ) with_max
  where shipment_num = orders_max_ship_num
)
select
  s.ord_num, s.shipment_num, s.item_code, s.qty_to_pick, s.qty_picked,
  od.unit_sales_price, od.unit_sales_price * s.qty_picked as total_price
from s
join order_det od on od.ord_num = s.ord_num and od.item_code = s.item_code
order by s.ord_num, s.item_code;

Or you apply the join right away:

select
  s.ord_num, s.shipment_num, s.item_code, s.qty_to_pick, s.qty_picked,
  od.unit_sales_price, od.unit_sales_price * s.qty_picked as total_price
from
(
  select t1.*, max(shipment_num) over (partition by ord_num) as orders_max_ship_num
  from pick_det t1
) s
join order_det od on od.ord_num = s.ord_num and od.item_code = s.item_code
where s.shipment_num = s.orders_max_ship_num
order by s.ord_num, s.item_code;

The AG · Answer 3 · 2021-09-08T14:50:16.543

Another simple technique you could use is by applying analytic function - Dense_Rank which is a window function that assigns a rank to each row within a partition of a result set

I am trying to rank by shipment_num in descending order and then just JOINing on the common column. I have used CTE and sub-query as well:

With temp AS(Select * from (SELECT a.*, dense_rank() OVER (PARTITION BY ord_num order by shipment_num desc) rnk
from Pick_det a) t where rnk = 1)

Select temp.Ord_num, temp.Shipment_num, temp.item_code, temp.Qty_to_pick, temp.Qty_picked,
temp.qty_to_pick * Order_det.Unit_sales_price total_price
from temp  JOIN Order_det on temp.ord_num = Order_det.ord_num
AND temp.item_code = Order_det.item_code;

Your Sub-query would look something like this:

Select temp.Ord_num, temp.Shipment_num, temp.Item_code, temp.Qty_to_Pick, temp.Qty_picked,
temp.qty_to_pick * Order_det.Unit_sales_price total_price
from 
(SELECT a.*, dense_rank() OVER (PARTITION BY ord_num order by shipment_num desc) rnk
from Pick_det a) temp INNER JOIN Order_det on temp.ord_num = Order_det.ord_num
AND temp.item_code = Order_det.item_code
AND temp.rnk = 1;

If you want to learn about dense_rank function, here is the link

Here is the SQL Fiddle

Ranking functions and Joins

3 Answers3