how to get latest record based on range of date in mysql

Issue

Hi Everyone i am trying to get latest record between two range of date
Suppose i have select date between ‘2022-08-01’ and ‘2022-08-03’, please help me out.

current table

id cust_name amount date
1 A 100 2022-08-01
2 A 150 2022-08-02
3 B 100 2022-08-01
4 B 300 2022-08-02
5 B 50 2022-08-03
6 B 100 2022-08-04
7 C 200 2022-08-02

Expected output-:

id cust_name amount date
2 A 150 2022-08-02
5 B 50 2022-08-03
7 C 200 2022-08-02

Solution

I filtered out the dates and chose the latest date per cust_name using row_num.

select   id
        ,cust_name  
        ,amount 
        ,date
from    (
         select   *
                 ,row_number() over(partition by cust_name order by date desc) as rn
         from     t
         where    date between date '2022-08-01' and date '2022-08-03'
         ) t        
where   rn = 1
id cust_name amount date
2 A 150 2022-08-02 00:00:00
5 B 50 2022-08-03 00:00:00
7 C 200 2022-08-02 00:00:00

Fiddle

Answered By – DannySlor

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published