CODEWITHSUNDEEP
mysqlsql
Alexander Trauzzi
Alexander Trauzzi

Reputation: 7396

Retrieving the most recent entry per user

If I have a table with the following structure and data:

id | user_id | created_at
-------------------------
 1 |       7 |    0091942
 2 |       3 |    0000014
 3 |       6 |    0000890
 4 |       6 |    0029249
 5 |       7 |    0000049
 6 |       3 |    0005440
 7 |       9 |    0010108

What query would I use to get the following results (explanation to follow):

id | user_id | created_at
-------------------------
 1 |       7 |    0091942
 6 |       3 |    0005440
 4 |       6 |    0029249
 7 |       9 |    0010108

As you can see:

Is there a way to accomplish this without using subqueries? Is there a name in relational algebra parlance that this procedure goes by?

Upvotes: 3

Views: 212

Answers (2)

eggyal
eggyal

Reputation: 125835

The query is known as a groupwise maximum, which (in MySQL, at least) can be implemented with a subquery. For example:

SELECT my_table.* FROM my_table NATURAL JOIN (
  SELECT   user_id, MAX(created_at) created_at
  FROM     my_table
  GROUP BY user_id
) t

See it on sqlfiddle.

Upvotes: 6

isJustMe
isJustMe

Reputation: 5470

You can just get the max and group by the user_id:

select id,user_id,max(created_at)
from supportContacts
group by user_id
order by id;

Here is what it outputs:

ID  USER_ID MAX(CREATED_AT)
1   7   91942
2   3   5440
3   6   29249
7   9   10108

See the working demo here

Note that the example on the fiddle uses the created_at field as int, just use your format it should make no difference.

EDIT: I will leave this answer as a referece but note that his query will produce undesired results as Gordon stated, please do not use this in production.

Upvotes: 2

Related Questions