Postgres SQL: How to select the highest value in the "media" column for the same time stamp in postgres?

Question

I am using possql through the DataGrip program. I have the following table:

    timestamp       | Channel |  media
-----------------------------------------
2020-04-29 00:00:00 |   3     |   1.2
2020-04-29 00:00:00 |   4     |    2
2020-04-29 00:00:00 |   5     |    1
2020-04-29 00:10:00 |   3     |    2
2020-04-29 00:10:00 |   4     |   1.5
2020-04-29 00:10:00 |   5     |    3

I would like to sort by the highest value in the "media" column for each "timestamp", as follows:

    timestamp       | Channel |  media
-----------------------------------------
2020-04-29 00:00:00 |   4     |    2
2020-04-29 00:10:00 |   5     |    3

How can I do this?

I tried to do this but it didn't work, it's repeating the original table:

SELECT timestamp, max(media), channel
FROM monitoring_aggregate
GROUP BY timestamp, channel
ORDER BY timestamp

score 1 · Accepted Answer · answered May 08 '20 at 16:31

1

In Postgres, just can just use distinct on to solve this top-1-per-group problem:

select distinct on (timestamp) ma.*
from monitoring_aggregate ma
order by timestamp, media desc

answered May 08 '20 at 16:31

GMB

216,147
25
84
135

Many thanks for the reply! And if you have 4 columns, to choose only 3 and do the same sorting, as I would do? – Guilherme Alves May 08 '20 at 17:36

Postgres SQL: How to select the highest value in the "media" column for the same time stamp in postgres?

1 Answers1