How to fetch distinct records by column and order by timestamp

Question

How to get the desired result by some my SQL query as I have tried group by and distinct by nothing seems to work fine in this case.

Records:

+----+---------+---------------------+
| id | user_id | created_at |
+----+---------+---------------------+
| 22 | 3 | 2019-09-13 06:35:29 |
| 23 | 3 | 2019-09-13 06:53:59 |
| 24 | 3 | 2019-09-13 06:54:00 |
| 25 | 3 | 2019-09-13 06:54:01 |
| 26 | 3 | 2019-09-13 06:54:02 |
| 27 | 3 | 2019-09-13 06:54:02 |
| 28 | 3 | 2019-09-13 06:54:03 |
| 29 | 3 | 2019-09-13 06:54:03 |
| 30 | 3 | 2019-09-13 06:54:05 |
| 31 | 3 | 2019-09-13 06:54:06 |
| 32 | 3 | 2019-09-13 06:54:06 |
| 33 | 3 | 2019-09-13 06:54:06 |
| 34 | 3 | 2019-09-13 06:54:10 |
| 35 | 3 | 2019-09-13 06:54:10 |
| 36 | 3 | 2019-09-13 06:54:11 |
| 37 | 1 | 2019-09-13 09:52:35 |
| 38 | 2 | 2019-09-13 09:53:46 |
| 39 | 2 | 2019-09-13 09:54:17 |
| 40 | 2 | 2019-09-13 09:54:18 |
| 41 | 2 | 2019-09-13 09:54:18 |
| 42 | 2 | 2019-09-13 09:54:19 |
| 43 | 2 | 2019-09-13 09:54:19 |
| 44 | 2 | 2019-09-13 09:54:20 |
| 45 | 2 | 2019-09-13 09:54:20 |
+----+---------+---------------------+

Expected Result:

+----+---------+---------------------+
| id | user_id | created_at |
+----+---------+---------------------+
| 36 | 3 | 2019-09-13 06:54:11 |
| 37 | 1 | 2019-09-13 09:52:35 |
| 45 | 2 | 2019-09-13 09:54:20 |
+----+---------+---------------------+

For better results next time, include your efforts in the question. For now, the duplicate link has you covered. — Tim Biegeleisen, Sep 13 '19 at 10:43
Use [max()](https://mariadb.com/kb/en/library/max/) and `GROUP BY user_id` ====> `SELECT max(id), user_id, max(created_at) FROM your-table GROUP BY user_id` — Roy Bogado, Sep 13 '19 at 10:44

score 0 · Answer 1 · answered Sep 13 '19 at 10:43

If you are using MySQL version 8.0 or higher you can do this by Window Function -

SELECT id, user_id, created_at
FROM (SELECT id, user_id, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) RN
      FROM YOUR_TAB)
WHERE RN = 1;

How to fetch distinct records by column and order by timestamp

1 Answers1