Get latest record by grouping id

Question

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.

So you want to find latest created_on for each s_id from table A right? — Arun, Dec 09 '21 at 11:02
why does your expected output contain two rows instead of one? — Salman A, Dec 09 '21 at 11:10
expecting lastest record for each status ,hence 2 rows @SalmanA — user, Dec 09 '21 at 11:14
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Dec 09 '21 at 12:23

score 0 · Accepted Answer · answered Dec 09 '21 at 11:07

0

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

answered Dec 09 '21 at 11:07

Dordi

778
1
5
14

Get latest record by grouping id

1 Answers1