Kuldeep Dangi Kuldeep Dangi - 6 months ago 15
SQL Question

Retrieving the last record in each group without primary key

I have a table to record user last login detail

event_name userID value created_at
-------------------------------------------------------------
last_login 12 2016-06-02 11:06:29 2016-06-02 11:06:29
last_login 12 2016-06-02 10:16:11 2016-06-02 10:16:11
last_login 14 2016-06-02 10:11:13 2016-06-02 10:11:13
last_login 17 2016-05-02 10:16:29 2016-05-02 10:16:29
last_login 14 2016-05-02 10:15:11 2016-06-02 10:15:11


Expected result:

event_name userID value created_at
-------------------------------------------------------------
last_login 12 2016-06-02 11:06:29 2016-06-02 11:06:29
last_login 14 2016-06-02 10:11:13 2016-06-02 10:11:13
last_login 17 2016-05-02 10:16:29 2016-05-02 10:16:29


I want to grab this table information group by UserId , I made it order by created_at and showing value in timesAgo fromat(e.g 1hour ago) but I amm not getting latest record on using group by for example , for userID = 12 I am getting value =2016-06-02 10:16:11 instead of value = 2016-06-02 11:06:29

Answer

If I understood you correctly, you can achieve this with NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS(SELECT 1 FROM YourTable s
                 WHERE s.created_at > t.created_at
                    AND s.userID = t.userID)