Choose only one row for a given value w Distinct
If you have a table with many rows but you only want one row for a given value, you can use
distinct on (<column>)
select distinct on (letter) * from ( values ('y', 512), ('y',128), ('z', 512), ('x',128), ('z', 256)) as x (letter, number);
letter | number --------+-------- x | 128 y | 512 z | 512 (3 rows)
Distinct implicitly orders by the specified column ascending. Putting
order by letter at the end of the select statement produces the exact same output (and execution plan).
Distinct chooses the first row for the given column after sorting, so changing the sort order for the second column will change the results.
Here are the results after adding a
order by letter, number asc to the above select statement.
letter | number --------+-------- x | 128 y | 128 z | 256 (3 rows)