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 ()`
``` sql
select distinct on (letter) *
from (
values
('y', 512),
('y',128),
('z', 512),
('x',128),
('z', 256))
as x (letter, number);
```
Which produces
``` sql
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.
``` sql
letter | number
--------+--------
x | 128
y | 128
z | 256
(3 rows)
```
chriserin
April 27, 2019