CODEWITHSUNDEEP
mysqlsql
alnet
alnet

Reputation: 1233

How to extract latest rows from Database for specific column?

I have the following table in the database.

+----+---------+---------------------+-----------+
| id | user_id | timeid              | course_id |
+----+---------+---------------------+-----------+
| 02 |       2 | 2016-01-27 19:24:17 |        14 |
| 03 |       2 | 2016-02-27 19:24:27 |        15 |
| 04 |       2 | 2016-03-27 19:24:37 |        16 |
| 05 |       3 | 2016-01-27 19:24:17 |        19 |
| 06 |       3 | 2016-02-27 19:24:27 |        18 |
| 07 |       3 | 2016-03-27 19:24:37 |        17 |
+----+---------+---------------------+-----------+

How can I extract only the latest rows sorted by timeid for each user_id?

In other words:

| 04 |       2 | 2016-03-27 19:24:37 |        16 |
| 07 |       3 | 2016-03-27 19:24:37 |        17 |

Upvotes: 0

Views: 48

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT  t1.id, t1.user_id, t1.timeid, t1.course_id
FROM mytable as t1
JOIN (
   SELECT user_id, MAX(timeid) AS timeid
   FROM mytable
   GROUP BY user_id
) AS t2 ON t1.user_id = t2.user_id AND t1.timeid = t2.timeid

t2 is a derived table containing the latest timeid value per user_id. If you join the original table to this you can get back all field of the record having the latest-per-group value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

A simple way uses = and a correlated subquery:

select t.*
from t
where t.timeid = (select max(t2.timeid) from t t2 where t2.user_id = t.user_id);

Upvotes: 2

Qammar Feroz
Qammar Feroz

Reputation: 718

You can use mysql query like

SELECT id, user_id, timeid, course_id FROM tablename ORDER BY timeid DESC GROUP BY user_id;

Upvotes: 0

Related Questions