CODEWITHSUNDEEP
phpsql
Tom-pouce
Tom-pouce

Reputation: 798

How to select MAX for each type in one request

I use mysql 5.1.

This is my DB schema :

id     user_id     type     created_at
1       32          X       2012-11-19
2       32          Y       2012-11-18
3       30          X       2012-11-16
4       32          Z       2012-11-17
5       31          Y       2012-11-13
6       32          Z       2012-11-9

I want that my SQL query returns the user_id, type, and the lastest created_at for each type. I tried to make this request: SELECT max(type) FROM notification WHERE user_id=34 ORDER BY type but it returns only the latest created_at.

Upvotes: 0

Views: 98

Answers (5)

Jhonathan H.
Jhonathan H.

Reputation: 2713

SELECT max(type) FROM notification WHERE user_id=34 ORDER BY type

try to revise like this

SELECT user_id, type,created_at FROM notification WHERE user_id=34 ORDER BY type

then if you want to see from latest to oldest or oldest to lastest

add ASC or DESC order

Upvotes: 0

Steve Homer
Steve Homer

Reputation: 3922

select n.id, n.user_id, n.type, m.max_created_at
from notification n
     inner join
     (
         select   type, max(created_at) as max_created_at
         from     notification
         group by type
     ) m  on n.type = m.type and 
             m.max_created_at = n.created_at

Note this does assume that there are not two records with the same type and created_at date.

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

The following query selects the highest created_at for each type:

SELECT type, MAX(created_at) FROM notification GROUP BY type ORDER BY type

Not sure how you want to combine that with user, your question isn't precise enough.

The following query selects the highest created_at for each type for a specific user:

SELECT user, type, MAX(created_at) 
  FROM notification 
 WHERE user = 34
 GROUP BY type, user 
 ORDER BY type, user

Upvotes: 0

Vyktor
Vyktor

Reputation: 21007

You cannot do this in really simple query because GROUP BY (which you are missing by the way in your query) doesn't guarantee that it won't return values mixed from multiple rows.

SELECT * FROM notifications AS t1
INNER JOIN (
    SELECT type, MAX(`created_at`) AS max_created_at
    FROM notification
    WHERE user_id=34
    GROUP BY `type`
) AS t2
    ON t1.type = t2.type
        AND t1.created_at = t2.max_created_at

Upvotes: 1

xelber
xelber

Reputation: 4637

SELECT user_id, type, max(created_at) FROM notification WHERE user_id=34 GROUP BY user_id,type ORDER BY type

Upvotes: 0

Related Questions