How can I select rows corresponding to the unique pair of column values with the highest value of another column in PostgreSQL?

Question

My table looks like this:

A	B	X
1	1	1
1	1	2
1	1	3
1	2	1
1	2	2
2	2	1
2	2	2
2	2	3

I need to select the row with the highest value in X column for each unique A, B pair.

The result would be:

A	B	X
1	1	3
1	2	2
2	2	3

Answer 1

I would recommend distinct on:

select distinct on (a, b) t.*
from t
order by a, b, x desc;

This allows you to select other columns from the rows, other than a, b, and x.

With an index on (a, b, x desc), this would typically be the fastest solution.