How to get last N rows of each group from group by in MySQL

Question

I have a table with isin (test), date (Date), and amount (int).

I am trying to get the amount of the last 2 dates per isin.

Someone can tell me the trick please?

• Akina: Last 2 dates are first 2 dates when the ordering is reversed.
• Rick James: See the tag I added or Groupwise-Max

Answer 1

With MySQL 8 you can use a CTE with then Window function ROW_NUMBER to get your wanted data

WITH CTE AS(SELECT
isin , date , amount ,
ROW_NUMBER() OVER(PARTITION BY isin ORDER BY date DESC) rn
FROM mytable)
SELECT 
isin , date , amount
FROM CTE
WHERE rn <= 2
ORDER BY isin, rn