CODEWITHSUNDEEP
mysqlsql
Ran
Ran

Reputation: 3523

Mysql - getting distinct records

I have the following MySql table (user_actions) with the following columns: id, user_id, created_at, action_type

I want a sql query that will get the latest actions that the user performed with no duplication of the actions.

for example: user_id 1 has 3 records that has the action_type "follow" and 2 records that has the action_type "unfollow"

in this case i want the query to return two records, one with action_type "follow" and one with "unfollow"

any thoughts?

Upvotes: 1

Views: 95

Answers (3)

anubhava
anubhava

Reputation: 784918

Try this query:

select * from user_actions where action_type, created_at in 
   (select action_type, max(created_at) from user_actions group by action_type);

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

try this:

select ua.* 
from user_actions ua
join (select max(id) as max_id,user_id,action_type from user_action group by user_id,user_action) ua_max
on ua.id=ua_max.max_id and ua.user_id=ua_max.user_id and ua.action_type=ua_max.action_type

Upvotes: 1

Greg
Greg

Reputation: 33650

You can use SQL's group by clause for this:

select user_id, action_type, max(created_at)
  from user_actions
 group by user_id, action_type

Upvotes: 2

Related Questions