CODEWITHSUNDEEP
phpsql
Tom-pouce
Tom-pouce

Reputation: 798

SQL Query, use MAX function or equivalent

Here is my problem, I have this request (it works) but I would like to replace the TO_DAYS(notification.created_at) by the latest action of the user.

I tried to use TO_DAYS(MAX(notification.created_at)) but it returns "#1111 - Invalid use of group function".

SELECT user.email, notification.type, max(notification.created_at) AS date 
    FROM user, notification 
    WHERE (user.id = notification.user_id) AND (TO_DAYS(NOW()) - TO_DAYS(notification.created_at) >= 30)
    GROUP BY user.id 
    ORDER BY `date` DESC

Upvotes: 0

Views: 173

Answers (2)

Philipi Willemann
Philipi Willemann

Reputation: 658

When using aggregate functions like MAX, you have to use HAVING and not WHERE, it's possible to use both on the same command, however HAVING will always come after WHERE and GROUP BY.

for more info : http://www.w3schools.com/sql/sql_having.asp

Upvotes: 0

shadyyx
shadyyx

Reputation: 16055

First of all I do not understand why not using LEFT JOIN in Your query... Secondly it should be enough to convert to days the subtraction itself... Thirdly, that condition from WHERE should go into HAVING... Fourthly, I guess that date in SELECT should also be escaped... It could be:

SELECT u.email, n.type, max(n.created_at) AS `date`
FROM user u
LEFT JOIN notification n ON u.id = n.user_id
GROUP BY user.id 
HAVING TO_DAYS(NOW() - MAX(n.created_at)) >= 30
ORDER BY `date` DESC

Try this one...

Upvotes: 1

Related Questions