如何根据最大日期获得Id

Question

我有以下表格(显示了示例数据)

Id  WayBillNo       Date                       Status
------------------------------------------------------------------------------
1   501263027308    2023-03-10 23:59:59.000    DELIVERED
2   501263027308    2023-03-10 09:01:12.000    OUT FOR DELIVERY
3   501263027308    2023-03-09 16:53:25.000    IN-TRANSIT
4   501263027308    2023-03-09 03:40:37.000    IN-TRANSIT
5   501263027308    2023-03-08 01:36:24.000    ARRIVED AT DESTINATI
6   501263027308    2023-03-03 15:59:23.000    IN-TRANSIT
7   501263027308    2023-03-01 20:50:38.000    IN-TRANSIT
8   501263027312    2023-03-05 12:11:29.000    ARRIVED AT DESTINATI
9   501263027312    2023-03-04 23:27:27.000    IN-TRANSIT
10  501263027312    2023-03-03 19:11:06.000    IN-TRANSIT
11  501263027312    2023-03-02 14:26:06.000    IN-TRANSIT
12  501263027312    2023-03-01 20:50:38.000    IN-TRANSIT

我希望输出如下(即，基于个人方式账单号的最大日期的id):

Id
---
1   
8

请帮我一下。

Answer 1

我解决了:

select *
from tbl S
inner join (
select WayBillNo,
MaxDate=max([date])
from tbl
group by WayBillN
) T on T.WayBillNo=S.WayBillNo and T.MaxDate=S.Date

Answer 2

下面的查询将为您获取所需的结果，

选择Id从TestTable在DateCol在(从TestTable表中选择MAX(DateCol)不)

Answer 3

select id
from table_name1 
where case when date in (
select max_date as date
from (
select max(date) as max_date
from table_name1
group by waybillno
) t1
) then 1 else 0 end 

我们可以用group by

Answer 4

select Id
from table one
inner join (
select WayBillNo, max(Date) as MaxDate from table group by WayBillNo)
two on one.WayBillNo=two.WayBillNo and one.Date=two.MaxDate