MYSQL - select the first 4 records for each category in the table

Question

I have an INVENTORY table that has, among other columns, CATEGORY and UPDATED . Sorting a table by these columns is simple:

SELECT * FROM INVENTORY ORDER BY CATEGORY ASC, UPDATED ASC

I want to get a result set containing only the first 4 rows from each category. Any idea how to do this?

Answer 1

Just like How to limit the SQL result set to not too common elements

You can try something like

 SELECT * FROM ( SELECT *, ( SELECT COUNT(1) FROM INVENTORY WHERE CATEGORY = i.CATEGORY AND UPDATED < i.UPDATED ) CountTotal FROM @INVENTORY i ) sub WHERE sub.CountTotal <= 3