1

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?

2
  • Last 2 dates are first 2 dates when the ordering is reversed.
    – Akina
    Apr 11 at 4:25
  • See the tag I added or Groupwise-Max
    – Rick James
    Apr 27 at 4:29

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.