MySQL: How can I modify this query to return each user just once?
This query is suppose to return distinct users but it returns the same user a few times, I want it to only return each user once (and return that user's row with the highest total).
SELECT DISTINCT a.user AS userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' ) AS HOUR , COUNT( a.id ) AS total
FROM posts a
LEFT JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP BY userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' )
ORDER BY total DESC , b.posts DESC ;
SELECT DISTINCT a.user AS userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' ) AS HOUR , COUNT( a.id ) AS total
FROM posts a
LEFT JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP BY userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' )
ORDER BY total DESC , b.posts DESC ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mehdi_javan: With that query, the 'total' is off.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mehdi_javan: I ran the query you gave me but total is off. The Total is suppose to a count of posts made within any hour throughout the day but with your modification, it no longer does that, instead it counted all the posts made within the entire day.
ASKER
zappafan2k2: With your query, the total is off. It does the same as it did with mehdi_javan's query (refer to what I said to him).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
zappafan2k2: Yes exactly.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
zappafan2k2: In a situation like that, it doesn't matter which one is selected.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
zappafan2k2: I get this error with that query:
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
ASKER
zappafan2k2: I'm assuming you meant this query?
SELECT userid, max(q1.HOUR) AS HOUR, max(q1.total) AS total FROM
(SELECT a.user AS userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' ) AS HOUR , COUNT( a.id )) AS total
FROM posts a
INNER JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP BY userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' )) as q1;
If so, that one returns this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM posts a
INNER JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP ' at line 3
SELECT userid, max(q1.HOUR) AS HOUR, max(q1.total) AS total FROM
(SELECT a.user AS userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' ) AS HOUR , COUNT( a.id )) AS total
FROM posts a
INNER JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP BY userid, DATE_FORMAT( FROM_UNIXTIME( date ) , '%H' )) as q1;
If so, that one returns this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM posts a
INNER JOIN users b ON a.user = b.id
WHERE a.date >1325397600
GROUP ' at line 3
ASKER
We're getting close, anyone?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER