Today I Learned

A Hashrocket project

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)
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.