SQL DISTINCT: Remove Duplicate Result Rows Deliberately

The practice task

A course signup list contains repeated city and delivery-mode combinations. Produce the unique combinations, then explain why selecting only city answers a different question.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE signups(city TEXT, mode TEXT);
INSERT INTO signups VALUES ('Lahore','online'),('Lahore','online'),('Lahore','in-person'),('Karachi','online'),('Karachi','online');

SELECT DISTINCT city, mode FROM signups ORDER BY city, mode;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The result has three combinations. Lahore remains twice because its delivery modes differ. Selecting DISTINCT city alone would return two cities, which would be correct only if the report no longer needed the delivery-mode distinction.

The original five signup rows are unchanged. A unique result list is useful for a filter menu or category summary, but it does not establish that the underlying signups are invalid duplicates. Each could represent a different attendee.

For record-level deduplication, first define the identity and retention rule. A row with a newer timestamp, a verified source or a different attendee ID may need to remain. DISTINCT cannot make that business decision, and adding it after an incorrect join can hide the multiplication that needs investigation.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Inspect all selected columns when a supposedly duplicate row remains.
• Use ORDER BY when the displayed order matters.
• Do not use DISTINCT as an unexplained repair for a repeated-key join.