Ricardo Racciore Ricardo Racciore - 4 months ago 11
SQL Question

Get the X rows with most repeated records

I was wondering on how can I get the X number of elements with the most repeated elements on a table. For example, on a table like:

ID* | ID_USER | ID_PRODUCT | ACCESS_DATE


  • ID is the primary key.

  • ID_USER can be repeated meaning that the most rows we can find with that user ID, the most products he bought.



What I want to get is, for example, the 5 users who bought the most products.

Answer

Use aggregate function count ( http://dev.mysql.com/doc/refman/5.7/en/counting-rows.html ) with GROUP BY

SELECT ID_USER, count(*) as num_b 
FROM YOUR_TABLE
GROUP BY ID_USER
ORDER BY num_b DESC
LIMIT 5;