Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
SELECT DISTINCT returns each result row once, however many times it occurs. It answers questions of the form “which values appear in this column”, which is often the first thing you want to know about a table you have not met. Open the client with mysql -u root -p sakila before you start.

​
How DISTINCT works

Replace COLUMN_LIST with the columns you want and TABLE_NAME with the table. DISTINCT sits immediately after SELECT and applies to the whole select list, not to the column next to it. That single fact explains most surprises with it.

​
Examples

​
1) The values in one column

Sakila rates every film. This asks which ratings exist.
5 ratings across 1,000 films. They arrive in no useful order, because DISTINCT removes duplicates and says nothing about order. Add ORDER BY when you want one.

​
2) Another column

Every film in the catalog is priced at 1 of 3 rates.

​
3) Distinct combinations

Name 2 columns and you get each distinct pairing, not each column’s values separately.
5 ratings times 3 rates is 15, and every combination happens to occur.

​
Why the sorted ratings are not alphabetical

That result is sorted by rating, yet it runs G, PG, PG-13, R, NC-17. Alphabetically NC-17 would come second. ORDER BY did work, and the reason is the column’s type: SHOW COLUMNS lists a table’s columns, and the pattern after LIKE narrows that to 1 name.
rating is an ENUM, a column type that accepts only a fixed list of values. MySQL stores each value as its position in that list and sorts by the position, so an ENUM sorts in the order the values were declared. Here the declared order runs from the least restrictive rating to the most restrictive.

​
Adding a column can undo it

Because DISTINCT looks at the whole row, adding a column that is nearly unique removes almost nothing.
All 1,000 rows come back. Each title is different, so each rating and title pair is different, and nothing is a duplicate. DISTINCT cannot give you 5 rows here, because you asked for a column that has 1,000 different values in it. Collapsing many rows into 1 per rating is a different operation, and GROUP BY is the clause for it.

​
Summary

  • Use SELECT DISTINCT to return each result row once.
  • Expect it to apply to every column in the select list at once.
  • Add ORDER BY when you want the distinct values sorted.
  • Expect an ENUM column to sort by its declared order rather than alphabetically.

​
See also

  • SELECT — the statement DISTINCT modifies
  • ORDER BY — sorting the distinct values
  • GROUP BY — collapsing rows into 1 per value, with a count