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?

Answer 1

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;