get the latest record from the table for each user

Question

I have a table user_status, which stores the status for each user, everytime a status changes, i apppend the status in the 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 I am looking for is the last status for each user based on the created date so the output I am looking is

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 I do that, I am also interested to know if there is a way to get the second to last status (one before the last) status for each user

so output from this query i am looking ofr is

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

score 0 · Answer 1 · answered Aug 20 '17 at 23:26

0

Window functions to the rescue!

select t.*
from (select t.*,
             row_number() over (partition by id order by created desc) as seqnum,
             nth_value(status, 2) over (partition by id order by created desc) as second_status
      from t
     ) t
where seqnum = 1;

If you don't want all the columns, you can use conditional aggregation:

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

answered Aug 20 '17 at 23:26

Gordon Linoff

1,242,037
58
646
786

Any reason for an anonymous downvote on what looks like a correct answer? – Gordon Linoff Aug 20 '17 at 23:28
i am not sure who did the downvote, I am still figuring out your query. i am not sure who did the downvote – user641887 Aug 20 '17 at 23:29
@user641887 . . . It looks like a malicious downvoter, who simply downvoted the question and all answers. – Gordon Linoff Aug 20 '17 at 23:30
@GordonLinoff gave you an upvote ;) – Naguib Ihab Aug 20 '17 at 23:30

score 0 · Answer 2 · answered Aug 21 '17 at 05:49

Looking at your query, i would suggest to get the details in one go rather than getting the first status and then going for the second one. Hope this below snippet helps.

SELECT B.ID,
  B.STATUS,
  B.CREATED
FROM
  (SELECT A.ID,
    A.STATUS,
    CREATED,
    ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY A.CREATED DESC) RN
  FROM
    (SELECT 1 ID,'ACTIVE' STATUS,'08-10-2017 08:21:22' CREATED FROM DUAL
    UNION ALL
    SELECT 1 ID,'ACTIVE' STATUS,'08-10-2017 08:21:25' CREATED FROM DUAL
    UNION ALL
    SELECT 1 ID,'NON_ACTIVE' STATUS,'08-10-2017 08:22:23' CREATED FROM DUAL
    UNION ALL
    SELECT 2 ID,'NON_ACTIVE' STATUS,'08-10-2017 08:22:23' CREATED FROM DUAL
    UNION ALL
    SELECT 2 ID,'ACTIVE' STATUS,'08-11-2017 08:25:23' CREATED FROM DUAL
    UNION ALL
    SELECT 3 ID,'NON_ACTIVE' STATUS,'08-12-2017 08:23:23' CREATED FROM DUAL
    )A
  )B
WHERE B.RN IN (1,2);
/

get the latest record from the table for each user

2 Answers2