MySQL Forums
Forum List  »  Newbie

Query help getting the latest value of a grouped result
Posted by: henk janssen
Date: November 02, 2021 02:06PM

Hello all,

I have a question about a query, I am struggeling with this one a lot, I have spend many hours on this problem.

I have this table coins:

ID Name
1 1INCH-EUR
2 AAVE-EU
3 ADA-EUR
4 ADX-EUR
5 AE-EUR

And this table track:

TrackID CoinID Price Time
1 1 4.2876 2021-10-29 15:43:23
2 2 288.34 2021-10-29 15:43:23
3 3 1.7212 2021-10-29 15:43:23
4 4 1.0542 2021-10-29 15:43:23
5 1 4.2876 2021-10-29 15:43:24
6 2 285.38 2021-10-29 15:43:24
7 3 1.7212 2021-10-29 15:43:24
8 4 1.0602 2021-10-29 15:43:24

And i want this result:

TrackID CoinID Name MinPrice MaxPrice CurrentPrice
5000 1 1INCH-EUR 3.8927 3.9705 3.9000
5001 2 AAVE-EUR 275.89 278.38 277.20
5002 3 ADA-EUR 1.695 1.7059 1.7059
5003 4 ADX-EUR 0.77537 0.78602 0.77680
5004 5 AE-EUR 0.064378 0.064378 0.064378
5005 6 AION-EUR 0.15594 0.15679 0.15598
5006 7 AKRO-EUR 0.033574 0.034416 0.034123
5007 8 ALGO-EUR 1.5876 1.6048 1.5954

I have this query to get all except the currentprice:

SELECT DISTINCT T.TrackID ,T.ID AS 'CoinID', C.Name,
MIN(T.Price) AS 'MinPrice', MAX(T.Price) AS 'MaxPrice'
FROM `track` AS T
LEFT JOIN coins AS C ON C.ID = T.ID
WHERE T.Time > NOW() - INTERVAL 1 HOUR
GROUP BY Name;
I can't figure out how to get the currentprice (the most recent(By timestamp) Price value on the track table belonging to that row)

And is this possible to solve with a windowing function?

Options: ReplyQuote


Subject
Written By
Posted
Query help getting the latest value of a grouped result
November 02, 2021 02:06PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.