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);

Which produces

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)