MYSQL - Return most recent 5 rows based on a certain field

Question

I have a table with a unix time stamp and a thing description. The table will have multiple entries for each thing, all with different time stamps. I need to select the last five time stamps for each thing.

If I have 100 rows for thing A, 130 rows for thing B and 20 rows for thing C I want to see the last 5 for each thing - the query should return 15 records.

I've tried limit but that gives me the last five in the query.

Any suggestions would be appreciated.

Answer 1

You could use ROW_NUMBER (MySQL 8.0):

SELECT *
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY time_col DESC) rn
      FROM tab) sub
WHERE rn <= 5;

Answer 2

For 100 rows, you can easily do:

select t.*
from t
where (select count(*)
       from t t2
       where t2.thing = t.thing and t2.timestampe <= t.timestamp
      ) <= 5;