How to get Id as per the max date

Question

I have the following table (with sample data shown)

    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

I want the output as below (i.e., the Ids based on the max date for individual way bill no):

Id
---
1   
8

Please help me on this.

@DaleK.. I was able to get the waybill numbers as per the max dates but not by Ids.. select WayBillNo, max([date]) from tbl group by WayBillNo — DeepakVerma, Mar 30 '23 at 06:13
You can use the following trick to get the maxest rows: select top 1 with ties * from yourtable order by row_number() over(partition by WayBillNo order by Date DESC); — siggemannen, Mar 30 '23 at 09:45

score -1 · Accepted Answer · edited Mar 30 '23 at 06:22

-1

I resolved it:

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

edited Mar 30 '23 at 06:22

Dale K

25,246
15
42
71

answered Mar 30 '23 at 06:18

DeepakVerma

1
2

score -1 · Answer 2 · edited Mar 30 '23 at 09:54

-1

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

We can use group by

edited Mar 30 '23 at 09:54

Dale K

25,246
15
42
71

answered Mar 30 '23 at 09:04

Ehsan Torkamani

14
3

score -1 · Answer 3 · answered Mar 30 '23 at 09:30

-1

The below query should fetch you the desired result,

SELECT Id FROM TestTable WHERE DateCol IN ( SELECT MAX(DateCol) FROM TestTable GROUP BY WayBillNo )

answered Mar 30 '23 at 09:30

Santosh

1
1

Mohit Savaliya · Answer 4 · 2023-03-30T11:24:59.900

-1

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

edited Mar 30 '23 at 11:24

answered Mar 30 '23 at 11:21

Mohit Savaliya

1
1

How to get Id as per the max date

4 Answers4