sql - get the latest record from the table for each user -

this question has answer here:

• fetch row has max value column 32 answers
• select second minimun value in oracle 3 answers
• oracle sql query: retrieve latest values per group based on time [duplicate] 2 answers

i have table user_status, stores status each user, everytime status changes, apppend status in user_status table.

id   status     created  1    active     08-10-2017 08:21:22 1    active     08-10-2017 08:21:25 1    non_active 08-10-2017 08:22:23 2    non_active 08-10-2017 08:22:23 2    active     08-11-2017 08:25:23 3    non_active 08-12-2017 08:23:23 

what looking last status each user based on created date output looking

id   status     created  1    non_active 08-10-2017 08:22:23 2    active     08-11-2017 08:25:23 3    non_active 08-12-2017 08:23:23 

also once that, interested know if there way second last status (one before last) status each user

so output query looking ofr

1    active     08-10-2017 08:21:25 2    non_active 08-10-2017 08:22:23 3    non_active 08-12-2017 08:23:23 

any suggestions,

window functions rescue!

select t.* (select t.*,              row_number() on (partition id order created desc) seqnum,              nth_value(status, 2) on (partition id order created desc) second_status       t      ) t seqnum = 1; 

if don't want columns, can use conditional aggregation:

select t.id, max(t.created) created,        max(case when seqnum = 1 status end) last_status,        max(case when seqnum = 2 status end) second_status, (select t.*,              row_number() on (partition id order created desc) seqnum       t      ) t group id;