postgresql Select all rows which have max value in one column

Question

I'm new to postgresql, I have a table T like this in postgresql:

C1 C2 C3 C4 ID

C1,C2, C4 are int.
C3 is a char C4 is essentially the version number. and can be anything between 1-N.
( For example, after one set of insert events the values in table are updated.) ID is a char.

Question:
For a given value of ID, I want to select all rows that have highest C4 associated with them. For example there could be N versions in the table, I want all results corresponding to the version N.

I tried,

select C1,C2,C3, max(C4) from T where ID = 'something';

but it gives me error.

Please advise.

Answer 1

update for postgresql version 13+

the latest version of postgresql introduces limit ... with ties

SELECT id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY c4 DESC
FETCH FIRST 1 ROW WITH TIES

Specifying WITH TIES will return all the rows where c4 equals the largest value in that columns, so a separate CTE with a window function is not required. If there is only 1 row where c4 has its maximum values, then only 1 row will be returned.

---

original answer (for earlier versions of postgresql):

If there can be multiple rows for the same version number, then use the window function RANK in a cte and select the rows where rank equals 1

SELECT id, c1, c2, c3, c4 
FROM (
    SELECT 
      *
    , RANK() OVER (PARTITION BY id ORDER BY c4 DESC) c4rank
    FROM t
) ranked 
WHERE c4rank = 1
  AND id = 'something'

If you want the most recent version for all ids, just omit the condition id = 'something' from the above statement.

If there can only be 1 row for a given version number then use order by and limit 1

SELECT id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY c4 DESC
LIMIT 1

If you want the most recent version for all ids & there can only be 1 row per (id, version) combination

SELECT DISTINCT ON (id) id, c1, c2, c3, c4
FROM t
WHERE id = 'something'
ORDER BY id, c4 DESC