Filtering records by the larger date value in MySql

September 8, 2022

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 ...;