CODEWITHSUNDEEP
sqlsqlite
Korniltsev Anatoly
Korniltsev Anatoly

Reputation: 3686

Can't aggregate on max function

I have a table 

CREATE TABLE `messages` ( `uid` BIGINT NOT NULL ,
`mid` BIGINT , `date` BIGINT NOT NULL  , PRIMARY KEY (`mid`));

I want to select max(date) grouped by uid, i.e. for every uid(read user) I want to find the latest message (with tha maximum date)

tried this 

select messages.mid,  max(messages.date), messages.uid, messages.body 
                                   from messages 
                             where messages.chat_id is  NULL 
                                   group by messages.uid

but the query works wrong.

Upvotes: 0

Views: 119

Answers (2)

Naval
Naval

Reputation: 344

u need to group by all the fields while using aggregate functions :) using a subquery would sort out the problem.

SELECT messages.date,messages.uid, messages.mid, messages.body FROM messages WHERE messages.chat_id IS NULL AND messages.date IN (SELECT MAX(msg.date) FROM messages msg WHERE messages.chat_id IS NULL And msg.uid = messages.uid )

alternatively it can also be done using the 'having' clause

done :)

Upvotes: 1

Fred Sobotka
Fred Sobotka

Reputation: 5332

A subquery can give you the date you need in order to retrieve the newest message for each user:

SELECT messages.uid, messages.mid, messages.date, messages.body
FROM messages
WHERE messages.chat_id IS NULL
AND messages.date IN 
( SELECT MAX(m2.date) FROM messages m2 
  WHERE m2.uid = messages.uid AND m2.chat_id IS NULL 
)
;

Upvotes: 3

Related Questions