Getting Unique Values with DISTINCT

Sometimes databases are like people with bad memories — they write everything down, but forget what they already wrote. So you open a table with client cities, and there are ten "Berlins", five "Seattles", and a bunch of other repeats. That happens when the same city pops up for different clients. But you don’t want to build a marketing campaign for ten "Berlins" when it’s really just one city, right?

To pull out only unique values — no duplicates — there’s a handy command called DISTINCT. It’s like a magic mop: one swipe and all the duplicate rows are gone, leaving only what’s actually different.

DISTINCT lets you grab only unique rows from your query result. This is super useful when you need to get rid of repeating data, like:

• Unique products in orders.
• Unique client names.
• Unique combos of data, like "city + country".

How does DISTINCT work?

The DISTINCT syntax is simple and clear, just like SQL usually is:

SELECT DISTINCT column1, column2, ...
FROM table;

When you add DISTINCT to your query, the database makes sure every row in the result is unique.

Examples of using DISTINCT

Let’s start with some classic examples to see how DISTINCT works.

Example 1: Unique values from one column

Let’s say we have a students table with student data:

-- Table students

We want to know which cities the students come from. Let’s write a query

SELECT city
FROM students;

and we get this result:

Not exactly what we wanted :(

To get rid of the duplicates, you need to use DISTINCT:

SELECT DISTINCT city
FROM students;

Result:

Without DISTINCT we’d see "Seattle" three times, but we only wanted it once.

Example 2: Unique values from multiple columns

Now let’s say we want to get unique combos of "first name + last name", since there could be students with the same last name or even the same first name.

Let’s use the same students table:

-- Table students

Query:

SELECT DISTINCT first_name, last_name
FROM students;

Result:

So, DISTINCT works like a filter: it checks all the columns you list and only removes rows where all those columns match.

Example 3: Unique combos and sorting

Now let’s combine DISTINCT with ORDER BY to get unique values, sorted alphabetically by last name.

Let’s use the students table:

-- Table students

Query:

SELECT DISTINCT first_name, last_name
FROM students
ORDER BY last_name ASC;

Result:

Duplicate rows are gone, and last names are sorted alphabetically.

Example 4: Using with aggregations

What if we try to use DISTINCT with a function, like COUNT?

SELECT COUNT(DISTINCT city) AS unique_city_count
FROM students;

Result:

This query will return the number of unique cities. Pretty handy, right?

Features of how DISTINCT works

When you use DISTINCT, it’s important to know that it works across all the columns you list. If you add more columns to your query, the result might change.

Example 5: Why is context so important?

If you add extra fields to your query, it can affect which rows are unique.

SELECT DISTINCT first_name, city
FROM students;

Table students:

Result:

Now every "first name + city" combo is unique. So remember: uniqueness is defined by all the columns you list, not by each column separately.

Common mistakes when using DISTINCT

One of the most common mistakes with DISTINCT is not really understanding what your query is doing. For example, if you list too many columns, you might get a result that’s way off from what you expected, because uniqueness will be based on all the columns.

For example:

SELECT DISTINCT *
FROM students;

In this case, every row will be considered unique, because all columns are included.

Another mistake is using DISTINCT where you don’t need it. If you know your data is already unique (like a column that’s a primary key), then DISTINCT just adds unnecessary load to your DBMS.