> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL SELECT DISTINCT > How SELECT DISTINCT removes duplicate rows in MySQL, why it applies to the whole select list rather than one column, and what it does not sort. 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 ```sql theme={null} SELECT DISTINCT COLUMN_LIST FROM TABLE_NAME; ``` 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. ```sql theme={null} SELECT DISTINCT rating FROM film; ``` ```text theme={null} +--------+ | rating | +--------+ | PG | | G | | NC-17 | | PG-13 | | R | +--------+ 5 rows in set (0.00 sec) ``` 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 ```sql theme={null} SELECT DISTINCT rental_rate FROM film; ``` ```text theme={null} +-------------+ | rental_rate | +-------------+ | 0.99 | | 4.99 | | 2.99 | +-------------+ 3 rows in set (0.00 sec) ``` 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. ```sql theme={null} SELECT DISTINCT rating, rental_rate FROM film ORDER BY rating, rental_rate; ``` ```text theme={null} +--------+-------------+ | rating | rental_rate | +--------+-------------+ | G | 0.99 | | G | 2.99 | | G | 4.99 | | PG | 0.99 | | PG | 2.99 | | PG | 4.99 | | PG-13 | 0.99 | | PG-13 | 2.99 | | PG-13 | 4.99 | | R | 0.99 | | R | 2.99 | | R | 4.99 | | NC-17 | 0.99 | | NC-17 | 2.99 | | NC-17 | 4.99 | +--------+-------------+ 15 rows in set (0.00 sec) ``` 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. ```sql theme={null} SHOW COLUMNS FROM film LIKE 'rating'; ``` ```text theme={null} +--------+------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------------------------+------+-----+---------+-------+ | rating | enum('G','PG','PG-13','R','NC-17') | YES | | G | | +--------+------------------------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) ``` `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. ```sql theme={null} SELECT DISTINCT rating, title FROM film; ``` ```text theme={null} +--------+-----------------------------+ | rating | title | +--------+-----------------------------+ | PG | ACADEMY DINOSAUR | | G | ACE GOLDFINGER | | NC-17 | ADAPTATION HOLES | ... +--------+-----------------------------+ 1000 rows in set (0.00 sec) ``` 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](/docs/tutorial/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](/docs/tutorial/select) — the statement DISTINCT modifies * [ORDER BY](/docs/tutorial/order-by) — sorting the distinct values * [GROUP BY](/docs/tutorial/group-by) — collapsing rows into 1 per value, with a count