Postgresql Get Maximum value per day with corresponding time-CodePudding

I have the following table:

Date      | Time     | Value  | ReceivedTime
2022-04-01| 00:59:59 | 5      | 00:30:15
2022-04-01| 13:59:59 | 15     | 13:30:00
2022-04-02| 21:59:59 | 5      | 21:30:15
2022-04-02| 22:59:59 | 25     | 22:25:15
2022-04-02| 23:59:59 | 25     | 23:00:15
2022-04-03| 14:59:59 | 50     | 00:30:15
2022-04-03| 15:59:59 | 555    | 00:30:15
2022-04-03| 16:59:59 | 56     | 00:30:15

I want to get maximum value along with Date,ReceivedTime.

Expected Result:

Date       | Value  | ReceivedTime
2022-04-01 | 15     | 13:30:00
2022-04-02 | 25     | 23:00:15
2022-04-03 | 555    | 00:30:15

CodePudding user response：

This answer assumes that, in the event of two or more records being tied on a given day for the same highest value, you want to retain the single record with the most recent ReceivedTime. We can use DISTINCT ON here:

SELECT DISTINCT ON (Date) Date, Value, ReceivedTime
FROM yourTable
ORDER BY Date, Value DESC, ReceivedTime DESC;