CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Jack_Carver
Jack_Carver

Reputation: 77

postgresql - group by number and return highest value

my SQL table looks like this

+------+--------+
| RANK |  ID    |
+------+--------+
| 1    | 234    |
| 2    | 234    |
| 3    | 234    |
| 1    | 421    |
| 2    | 421    |
+------+--------+

I'd like to group it by id and return highest rank. I don't know how to do that, my code not works.

SELECT * FROM table GROUP BY ID WHERE max(RANK)

Upvotes: 0

Views: 3049

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Another method is distinct on:

select distinct on (id) t.*
from t
order by id, rank desc;

The advantage of distinct on is that you can easily get all columns in the row, not just the rank column.

Upvotes: 3

BShaps
BShaps

Reputation: 1414

You're close:

SELECT ID, max(RANK) FROM table GROUP BY ID

This answer assumes you want the following result:

+------+--------+
| RANK |  ID    |
+------+--------+
| 3    | 234    |
| 2    | 421    |
+------+--------+

Upvotes: 5

Related Questions