Tricky Postgresql query

Question

Given the following table.

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, b)
);

How do I get one row for every a such that c is the max of that a?

For example given the table below,

a|b|c
-----
1 1 1
1 2 2
2 1 9
3 2 4
3 3 5
3 4 6

I should get the back

a|b|c
-----
1 2 2
2 1 9
3 4 6

Answer 1

The trick is to find the max c for every a in a derived table that you join with, like this:

select a, b, c
from example
join (select a, max(c) max_c from example group by a) max_c
on example.a = max_c.a and example.c = max_c.max_c