Question 21062 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    If you are running MySQL 8 or later, then we can use ROW_NUMBER() here:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY balance) rn_min,
              ROW_NUMBER() OVER (PARTITION BY DATE(timestamp) ORDER BY timestamp DESC) rn_last
    FROM yourTable
)

SELECT
    DATE(timestamp) AS date,
    MAX(CASE WHEN rn_min = 1 THEN balance END) AS min_balance,
    MAX(CASE WHEN rn_last = 1 THEN balance END) AS last_balance
FROM cte
GROUP BY 1
ORDER BY 1;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search