I have the following table:
Deals
PositionID - INT
Deal - INT
Time - Datetime
For every position ID, there are 2 rows, 1 for opening the order and 1 for closing the order. The goal here is to get the larger date ( i.e. the closing date ).
When I execute the following script I get these fields.
select md.PositionID,md.Deal , md.Time from deals md
where md.PositionID in (5804179, 2027379)
The script should return:
5804179 -> 2022-06-28 08:12:58
2027379 -> 2021-02-14 23:21:21
I want to get the larger Time value for every PositionID. And I’ve tried to group by PositionID but not the results I wanted. So is there a way to do that ?
>Solution :
SELECT DISTINCT
PositionID,
FIRST_VALUE(Deal) OVER (PARTITION BY PositionID ORDER BY `Time` DESC) Deal,
MAX(`Time`) OVER (PARTITION BY PositionID) `Time`
FROM deals
-- WHERE ...;