Search code examples
mysqlsqlgroup-by

How to get the value of the last record in each group?

I have a table like this:

Table "*wallet"

amount balance timestamp
1000 1000 2023-01-25 21:41:39
-1000 0 2023-01-25 21:41:40
200000 200000 2023-01-25 22:30:10
10000 210000 2023-01-26 08:12:05
5000 215000 2023-01-26 09:10:12

And here is the expected result: (one row per day)

min_balance last_balance date
0 200000 2023-01-25
210000 215000 2023-01-26

Here is my current query:

SELECT MIN(balance) min_balance,
       DATE(timestamp) date
FROM wallet
GROUP BY date

How can I add last_balance? Sadly there is no something like LAST(balance) in MySQL. By "last" I mean bigger timestamp.

Solution

  • With MIN() and FIRST_VALUE() window functions:

    SELECT DISTINCT
       MIN(balance) OVER (PARTITION BY DATE(timestamp)) AS min_balance, 
       FIRST_VALUE(balance) OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) AS last_balance,
       DATE(timestamp) AS date
FROM wallet;

    See the demo.