SQL – Get multiple values when limit 1

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?

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;