连接和/或子查询或排序函数

Question

我有一个表如下:

<表类> Order_ID Ship_num Item_code Qty_to_pick Qty_picked Pick_date tbody><<tr>11111130000空111112299519652021-05-12111121300030002021-06-2411112210300空111132103010302021-08-23222213270622021-03-182222144320空2222232080空2222244322002021-05-212222332082082021-08-232222342322002021-08-25

使用max(ship_num)是一个好主意，但您应该使用分析版本(带有OVER子句)。

select *
from
(
select t.*, max(ship_num) over (partition by order_id) as orders_max_ship_num
from table1 t1
) with_max
where ship_num = orders_max_ship_num
order by order_id, item_code;

您可以使用DENSE_RANK()获得此值。

查询

;with cte as (
select rnk = dense_rank()
over (Partition by order_id order by ship_num desc)
, *
from table_name
)
Select *
from cte
Where rnk =1;