If I have a table like this:
ID | ident | product 1 | cucu1 | 99867 | 2 | kkju7 | 88987 | 3 | sjdu4 | 66754 | 4 | kjhu6 | 76654 | 5 | cucu1 | 98876 |
And use this query: SELECT ident,COUNT(*) FROM sales WHERE status=? AND team=? AND DATE(date) = DATE(NOW() - INTERVAL 1 DAY) GROUP BY ident order by COUNT(*) DESC LIMIT 1
I get the value: cucu1
, since that has the most rows.
But if my table is like this:
ID | ident | product 1 | cucu1 | 99867 | 2 | kkju7 | 88987 | 3 | sjdu4 | 66754 | 4 | kkju7 | 76654 | 5 | cucu1 | 98876 |
It should return both cucu1
and kkju7
, since they are the highest with same count, but still it gives me only cucu1
. What am I doing wrong?
Advertisement
Answer
You can use rank()
:
SELECT ident, cnt FROM (SELECT ident, COUNT(*) as cnt, RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum FROM sales WHERE status = ? AND team = ? AND DATE(date) = DATE(NOW() - INTERVAL 1 DAY) GROUP BY ident ) i WHERE seqnum = 1;