I am trying to build a query which gives me some users having max of sum of one field. I can get the max by limit 1. But as there can be possibility of having the same value of max for multiple users how can I achieve this?
For example I have table as ID, CONTACT_ID, ..., AMOUNT, ....
Currently I am using this kind of query:
SELECT SUM(AMOUNT) AS total,CONTACT_ID
FROM TABLE
WHERE ID = 1
GROUP BY CONTACT_ID
ORDER BY total DESC limit 1
If I try to use MAX() function. It gives me all the rows instead of max.
SELECT total,CONTACT_ID FROM
(SELECT SUM(AMOUNT) AS total,CONTACT_ID
FROM TABLE
WHERE ID = 1
GROUP BY CONTACT_ID
ORDER BY total DESC)
GROUP BY CONTACT_ID
HAVING total = MAX(total)
Aucun commentaire:
Enregistrer un commentaire