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 |
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 |