DISTINCT

DISTINCT removes duplicate rows from the result, considering every column you selected.

SELECT DISTINCT country FROM authors gives each country once. SELECT DISTINCT country, name gives distinct pairs, which for unique names removes nothing at all. That surprises people who expect it to apply to the first column.

Try it

Remove DISTINCT and count the rows. Then add name to the SELECT and put DISTINCT back: it no longer removes anything.

loading editor…

real SQLite, in your browser

Four rows: US, GH, NG, and NULL. DISTINCT treats all NULLs as one value here, which is one of the few places NULL is grouped with itself.

Reaching for DISTINCT to fix unexpected duplicates usually hides a problem rather than solving it. In module 5 you will see joins multiply rows. DISTINCT will make the row count look right while every SUM in the query stays wrong, which is worse than an obviously broken result.

◈ Graded Query

Return the title and year of the three most recently published books, newest first. Two books share a year, so break that tie alphabetically by title. Two columns: title, then year.

Checked against the rows your query returns. 1 visible and 2 hidden checks, some against data you cannot see.

loading editor…

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back