SQL: The PostgreSQL Way - PostgreSQL: Up and Running - page 125

DISTINCT ON
One of our favorites is the DISTINCT ON . It behaves like DISTINCT , but with two en‐
hancements: you can tell it which columns to consider as distinct and to sort the re‐
maining columns. The first row after the sort will be returned. One little word— ON —
replaces numerous lines of additional code to achieve the same result.
In Example 7-9 , we demonstrate how to get the details of the first tract for each county.
Example 7-9. DISTINCT ON
SELECT DISTINCT ON ( left ( tract_id , 5 ))
left ( tract_id , 5 ) As county , tract_id , tract_name
FROM census . lu_tracts
ORDER BY county , tract_id ;
county | tract_id | tract_name
-------+-------------+----------------------------------------------------
25001 | 25001010100 | Census Tract 101, Barnstable County, Massachusetts
25003 | 25003900100 | Census Tract 9001, Berkshire County, Massachusetts
25005 | 25005600100 | Census Tract 6001, Bristol County, Massachusetts
25007 | 25007200100 | Census Tract 2001, Dukes County, Massachusetts
25009 | 25009201100 | Census Tract 2011, Essex County, Massachusetts
:
The ON modifier can take on multiple columns, all of which will be considered to de‐
termine uniqueness. The ORDER BY clause has to start with the set of columns in the
DISTINCT ON ; then you can follow with your preferred ordering.
LIMIT and OFFSET
LIMIT returns only the number of rows indicated, and OFFSET indicates the number of
rows to skip. You can use them in tandem or separately. You almost always use them in
conjunction with an ORDER BY . In Example 7-10 , we demonstrate use of a positive offset.
Leaving out the offset is the same as setting the offset to zero.
These constructs are not unique to PostgreSQL and are in fact copied from MySQL,
although implementation differs widely among database products.
Example 7-10. First tract for counties 2 through 5
SELECT DISTINCT ON ( left ( tract_id , 5 ))
left ( tract_id , 5 ) As county , tract_id , tract_name
FROM census . lu_tracts
ORDER BY county , tract_id LIMIT 3 OFFSET 2 ;
county | tract_id | tract_name
-------+-------------+--------------------------------------------------
25005 | 25005600100 | Census Tract 6001, Bristol County, Massachusetts
25007 | 25007200100 | Census Tract 2001, Dukes County, Massachusetts
25009 | 25009201100 | Census Tract 2011, Essex County, Massachusetts