GreenStore's tables have no duplicate rows: each one has its id primary key, so no two are alike. And yet, as soon as you project only part of the columns, repetitions start appearing everywhere. Asking "which countries do I have customers in?" over a fifteen-row table returns fifteen answers when there are only three distinct countries. In this lesson you'll learn why that happens, how DISTINCT solves it, why it acts on the complete combination of the SELECT's columns (the most common misunderstanding of the module), where it fits in the logical execution order, and when the appearance of DISTINCT in a query is really a sign that the query is badly framed.

1. Why repeated rows appear
2. DISTINCT over one column
3. DISTINCT over several columns: the classic misunderstanding
4. Where DISTINCT fits in the logical order
5. DISTINCT combined with WHERE and with ORDER BY
6. COUNT(DISTINCT column): a preview of module 4
7. DISTINCT ON: PostgreSQL's extension
8. The cost of DISTINCT and when it gives away a mistake
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

Let's start with the problem:

15 rows. One per customer, because SELECT returns one result row for each source row. The projection has removed the columns that made each row unique (id, email, name...), and what's left repeats.

It's important to understand that this isn't a fault in the data. In the pure relational model a relation is a set and admits no duplicates, but SQL doesn't work with sets: it works with multisets (bags): it allows repetitions and only removes them if you ask explicitly. It was a pragmatic decision by the language's designers: removing duplicates costs time, and most queries don't need it.

The same with the cities:

It returns 15 rows with Valencia four times and Barcelona twice.

The DISTINCT keyword is written immediately after SELECT and removes repeated rows from the result:

3 rows. Now you've answered the question "which countries do I have customers in?".

11 rows out of the 15: Valencia appeared 4 times and Barcelona 2.

Another useful case: finding out which payment methods are actually used. The table's CHECK constraint allows four, but are they all used?

All four. And the statuses:

All five values of the domain are represented.

Very important: DISTINCT doesn't sort. The results above come out in an order PostgreSQL picks depending on how it removed the duplicates (usually with a hash table, whose order is unpredictable). You could perfectly well see France, Spain, Portugal. If you want a particular order, ORDER BY (section 5).

Two syntax details:

1. DISTINCT affects the whole column list, not the one next to it. SELECT DISTINCT country, city isn't "the distinct country and the city": that's section 3.
2. DISTINCT(country) works, but it's misleading: those parentheses belong to an expression, not to a function. SELECT DISTINCT (country), city still applies DISTINCT to both columns. Don't write DISTINCT with parentheses; it misleads whoever reads it.

And a note about nulls: for DISTINCT, all NULLs count as one and the same value and collapse into a single row. It's a deliberate exception to the rule that NULL isn't equal to NULL, and you'll see it in exercise 3.

This is the part to internalise properly:

11 rows, and Spain appears seven times. Didn't we say DISTINCT removes duplicates?

It has removed them. What's going on is that DISTINCT compares the complete row, not column by column. (Spain, Valencia) and (Spain, Madrid) are two different rows, even though they share the first value. A row is only removed if all of its values match another's: (Spain, Valencia) appeared four times in the table and one has been left.

Think of it this way: DISTINCT looks at the result row as if it were a tuple and asks itself "have I already seen exactly this tuple?".

Another example, this time on the catalogue: which combinations of category and supplier exist?

With ORDER BY so we can read it (we justify it in section 5):

12 combinations out of 20 products. There's a lot of business information to read in that table: category 4 (Drinks) is supplied by three different suppliers, whereas category 6 (Supplements) depends on a single one, and that single supplier —number 5, EcoNordic— is precisely the inactive one. Without DISTINCT you'd have had to read 20 rows to reach the same conclusion.

If what you really want is "one country per row and something about its cities", DISTINCT isn't the tool: you need to group, which is GROUP BY (lesson 04-05). The conceptual difference, to be clear about from now:

DISTINCT is applied after the SELECT's projection and before ORDER BY. That's consistent: you can only compare duplicate rows once you know which columns they contain.

Two rules follow from that, which you'll see in action right away:

7 Spanish cities. The order is: FROM brings the 15 rows → WHERE leaves 11 → SELECT projects city → DISTINCT reduces to 7.

Now the order is guaranteed (alphabetical). ORDER BY is the next lesson; here we use it only to make the results readable.

Try this:

The message is explicit and the reason is logical, not arbitrary. After the DISTINCT only three rows remain: Spain, Portugal and France. The Spain row comes from eleven original rows with eleven different cities. Which of the eleven should it be sorted by? The question has no answer, so PostgreSQL refuses to invent one.

The rule, worth memorising: with SELECT DISTINCT, everything that appears in ORDER BY must also appear in the SELECT list.

If what you wanted was to sort the countries by some criterion derived from the cities, you need GROUP BY and an aggregate function (module 4).

A frequent case that also fails for this reason: sorting by a column you don't project.

The same message. And if you "fix" it by adding price to the SELECT, you change the question: you'd go from "distinct categories" (6 rows) to "distinct combinations of category and price" (20 rows, because almost every price is unique). Adding columns to the SELECT weakens the DISTINCT.

Often you don't want the list of distinct values, but how many there are. For that you combine DISTINCT with the COUNT aggregate function:

Notice the difference between the three variants of COUNT:

Here COUNT(city) matches COUNT(*) because in GreenStore no customer has a null city, but the column allows nulls and in another database the difference would be visible.

We won't go deeper: COUNT, SUM, AVG, MIN and MAX are lesson 04-04, and grouping by category is 04-05. We leave it here simply because it's the most frequent application of DISTINCT in real analysis work.

PostgreSQL adds a variant that isn't in the SQL standard and that is enormously useful: DISTINCT ON (columns) keeps the first row of each group of values, according to the order you specify.

Problem: the most expensive product in each category.

Six rows, one per category, with the most expensive product in each. This is impossible with plain DISTINCT.

How it works, step by step:

1. ORDER BY category_id, price DESC sorts every row: first grouped by category, and inside each category, from most to least expensive.
2. DISTINCT ON (category_id) walks that sorted result and keeps the first row for each value of category_id, discarding the rest.

From which comes the golden rule:

The columns of DISTINCT ON (...) must be the first ones in the ORDER BY, and in the same order. If they aren't, PostgreSQL raises an error; and if the ORDER BY doesn't break ties properly after that, the row picked within each group is unpredictable.

If you change price DESC to price, you get the cheapest product in each category:

The "the most X row of each group" pattern (each customer's last order, each product's most recent review, the highest price in each category) is one of the most requested in the real world, and DISTINCT ON solves it in three lines.

Portability:

The portable form with window functions is studied in lesson 10-03, and it's the one you'll have to use if your SQL has to work outside PostgreSQL. While you're working with PostgreSQL, DISTINCT ON is shorter and usually faster.

DISTINCT isn't free. To know whether a row is repeated, PostgreSQL has to compare every row against the others, and it does so in one of two ways:

With 15 rows it's instantaneous. With ten million rows and a long text column, an unnecessary DISTINCT can turn a 50 ms query into a 30-second one. You'll be able to see it yourself with EXPLAIN in module 8.

And there's something more important than the cost: an unexpected DISTINCT is usually the symptom of a badly framed query, not the solution. The typical case arrives in module 3:

That query returns 20 rows and not 12, because Lucía Martínez has three orders (1, 5 and 15) and appears three times. The natural reflex is to add DISTINCT and relax. But the DISTINCT fixes nothing: it hides the fact that the join has multiplied the rows. If tomorrow you add a column to the SELECT —say o.order_date—, the rows duplicate again and the DISTINCT stops helping.

The checklist for when you catch yourself writing DISTINCT:

1. Are there really duplicates, or is my query creating them? If it's the latter, the problem is in the join, not in the projection.
2. Do I want "distinct values" or "one result per group"? If it's the latter, it's GROUP BY (04-05) or DISTINCT ON (section 7).
3. Do I want to check existence? Then the right tool is EXISTS (lesson 07-03), which doesn't multiply rows and therefore needs no deduplication.
4. Am I deduplicating by columns I don't need? Removing an unnecessary column from the SELECT can make the DISTINCT redundant.

DISTINCT is perfectly legitimate for what we've done in this lesson: asking "what distinct values are there in this column?". What's suspicious is using it as a patch.

• Believing that DISTINCT column1, column2 deduplicates only by the first one. It deduplicates by the complete combination. It's misunderstanding number one.
• Writing DISTINCT(column). It works, but it makes people think DISTINCT is a function applied to that column. It isn't. Write DISTINCT column1, column2.
• Expecting DISTINCT to sort. It doesn't sort. If you need order, ORDER BY.
• Sorting by a column that isn't in a SELECT DISTINCT. ORDER BY expressions must appear in select list. And "fixing it" by adding the column changes the question.
• Adding columns and not noticing that the DISTINCT stops helping. Each new column can multiply the result's rows.
• Using DISTINCT to cover up duplicates created by a JOIN. Fix the query, not the symptom.
• Forgetting that NULLs are grouped. DISTINCT returns a single row with NULL, even if there are a hundred.
• Using DISTINCT ON without the right ORDER BY. The row kept from each group is then unpredictable: one today, another tomorrow.
• Tip: to count unique values, COUNT(DISTINCT col) instead of fetching the list and counting it by hand.
• Tip: use DISTINCT as an exploration tool. SELECT DISTINCT status FROM orders; is the fastest way to find out what values a column actually holds, including the ones that shouldn't be there.
• Tip: if DISTINCT is slow, look at the plan. EXPLAIN (module 8) will tell you whether it's sorting on disk.

Marketing wants to know which cities purchases have been made from in Portugal and France. Write a query that returns the distinct combinations of country and city for the customers who aren't from Spain, sorted by country and city. Explain why the result has the number of rows it has.

A colleague writes this query to find out how many distinct payment methods are used and is surprised by the result:

Explain what it really returns, how many rows and why it doesn't answer their question. Then write the two correct queries: the one that gives the list of methods and the one that gives the number.

Using DISTINCT ON, get the most recent order of each customer who has purchased: customer_id, the order's id, order_date and status. Explain why the result has 12 rows and not 15, and what role the ORDER BY plays.

Solution 1

4 rows. The reasoning follows the logical execution order:

1. FROM customers → 15 rows.
2. WHERE country <> 'Spain' → 4 remain (customers 7, 8, 9 and 10). There's no null problem here because country is NOT NULL; if it allowed nulls, those customers would have been lost silently, as you saw in 02-03.
3. SELECT country, city → projects two columns of those 4 rows.
4. DISTINCT → looks for repeated combinations... and there aren't any, because the four foreign customers live in four different cities.

That is, in this particular case DISTINCT removes nothing. That doesn't make it useless: it guarantees that if a second customer registers in Lisbon tomorrow, the report will still be correct.

Solution 2

Your colleague's query returns the distinct combinations of payment method and status:

10 rows. card appears four times, once for each status in which there's an order paid by card. It doesn't answer "how many distinct payment methods are there" because DISTINCT deduplicates the complete pair, and the pairs are different even though they share the method.

The two correct queries:

The moral: every column you add to the SELECT can multiply the result's rows, because it weakens the equality condition DISTINCT uses. Before adding a column to a SELECT DISTINCT, ask yourself whether it still answers your question.

Solution 3

12 rows and not 15. The reason lies in the dataset's deliberate gaps: customers 13, 14 and 15 have never placed an order, so they don't appear in the orders table and there's nothing to group for them. A query over orders can only speak about customers who have ordered; to list the ones who never bought as well you need to combine both tables with a LEFT JOIN, which is exactly the content of lesson 03-03.

The ORDER BY plays a twofold role and both parts are essential:

• customer_id first because it's the DISTINCT ON column: PostgreSQL requires them to match, since it needs each customer's rows together to keep one.
• order_date DESC second because it determines which of each customer's rows survives: the first of the group, that is, the one with the highest date.

If you wrote ORDER BY customer_id, order_date (ascending), you'd get each customer's first order. And if you left out the second ORDER BY column, the row picked within each customer would be whichever the engine returned first, which can change between runs.

A quick check: Lucía Martínez (customer 1) has orders 1, 5 and 15, with dates 2025-03-04, 2025-05-07 and 2025-12-02. Number 15 came out, the most recent. Correct.

You now know how to handle duplicates:

• Repeated rows don't come from the data, but from the projection: when you remove columns, what's left repeats. SQL works with multisets and doesn't remove them unless you ask.
• DISTINCT is written right after SELECT and removes repeated rows by comparing the complete combination of projected columns, not just the first one.
• It's applied after the projection and before ORDER BY, which is where the restriction that everything you sort by must be in the SELECT comes from.
• NULLs collapse into a single row, as an exception to the general rule about nulls.
• COUNT(DISTINCT column) counts unique values and is the most common application in data analysis; the full aggregations arrive in module 4.
• DISTINCT ON (...) is a PostgreSQL extension that returns the first row of each group according to the ORDER BY, and solves the "the most recent / most expensive row of each X" pattern in three lines. Outside PostgreSQL it's done with window functions (module 10).
• DISTINCT costs (sorting or building a hash table) and, when it turns up by surprise, it usually gives away a badly framed query rather than solving a problem.

In the next lesson, Sorting Data with ORDER BY, you'll stop accepting whatever order the engine sees fit to give you. You'll see how to sort by one or several columns, how ties are resolved, how to sort by aliases and by calculated expressions, where PostgreSQL puts null values (and why MySQL puts them at the other end) and why linguistic collation can place a capital letter exactly where you don't expect it.