Get latest record by grouping id-CodePudding

My table a has s_id,status_id,created_on columns. Table structure - A:

s_id                       status_id     created_on
bbbef7457cf38d1e265955      54          2021-11-23 14:20:09
bbbef7457cf38d1e265955       5          2021-11-22 14:20:17
bbbef7457cf38d1e265955        1         2021-11-20 14:20:29
bbbef7457cf38d1e265955        7         2021-11-19 12:20:00

Another B table :

id  task             status
54  Mobile Number   SUCCESS
5   Mobile Number   FAILURE
1   Email           SUCCESS
7   EMail           Failure

i'm trying to pick lastest created_on entered for task mobile number in table A

query i tried

select
    max (case when id = 5 or id=54 then created_on end) as mobileNumber
from A where s_id='bbbef7457cf38d1e265955'

Output expected


MobileNumber                             EMail
2021-11-23 14:20:09             2021-11-20 14:20:29

this didnt work as expected.

CodePudding user response：

If you want lasted created_on by s_id, the query would be:

select
  s_id
  ,max (created_on) as mobileNumber
from A 
where s_id='bbbef7457cf38d1e265955'
  AND id in (5,54)
GROUP BY s_id

CodePudding user response：

It could be done by window functions:

with cte as (
    select a.*, row_number() over (
        partition by a.s_id, b.task
        order by a.created_on desc
    ) as rn
    from a
    join b on a.status_id = b.id
)
select *
from cte
where rn = 1