MYSql window function - get last value

Question

I am trying to fetch the last value of player balances in a certain time window.

I have a transactions table.

The player balance is not the maximum or the minimum one.

SELECT  project_id, 
        player_id,
        FIRST_VALUE(balance) OVER (PARTITION BY player_id ORDER BY event_arrival_time DESC) AS balance
FROM transactions
WHERE event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
        AND project_id='aaa' 
GROUP BY project_id, player_id

I get values, but if I test them using the query blown on a single player I get different balance and I see the given result balance somewhere in the middle of the period.

Also, if I run this query several times, I get different balance, like it chooses the different transaction (we are talking about 10 minutes difference).

SELECT * 
FROM transacitions
where event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
        AND project_id='aaa' and player_id = 'player1'
ORDER BY event_arrival_time desc

I want to get list of players in that period, and their latest balance (not MAX value - maybe max date).

Answer 1

Remove the GROUP BY clause and if needed use DISTINCT in SELECT:

SELECT DISTINCT 
  project_id, 
  player_id,
  FIRST_VALUE(balance) OVER (PARTITION BY player_id ORDER BY event_arrival_time DESC) AS balance
FROM transactions
WHERE event_arrival_time BETWEEN '2019-12-02 00:00:00' AND '2019-12-03 23:59:59'
  AND project_id='aaa'