It is often desirable to return the results of a query with no duplicates. The
keyword DISTINCT prevents duplicates from being returned. Figure
shows the use of DISTINCT to prevent duplicate states and duplicate
city and state combinations.
test=> SELECT state FROM friend ORDER BY state;
state
-------
MA
MA
MD
NJ
NJ
PA
(6 rows)
test=> SELECT DISTINCT state FROM friend ORDER BY state;
state
-------
MA
MD
NJ
PA
(4 rows)
test=> SELECT DISTINCT city, state FROM friend ORDER BY state, city;
city | state
-----------------+-------
Boston | MA
Plymouth | MA
Cedar Creek | MD
Ocean City | NJ
Williamsport | PA
(5 rows)
Notice that DISTINCT operates only on the columns selected in the query. It does not compare nonselected columns when determining uniqueness. Section explains how counts can be generated for each of the distinct values.