Using the DISTINCT keyword causes this error: not a SELECTed expression

Deep dive into DISTINCT and ORDER BY

When you use DISTINCT, SQL returns only unique rows from your result set. However, combining DISTINCT with ORDER BY can be like herding cats - SQL struggles to ensure the rows are both unique and sorted! This is an issue especially when your ORDER BY includes columns calculated or derived from various joins.

When there's an attempt to order by a column that isn’t in the SELECT DISTINCT list of a query, you're likely to run into an ORA-01791: not a SELECTED expression error. You wouldn't want SQL to feel left out, would you?

The trick is to ensure that every column in your ORDER BY is also present in your SELECT DISTINCT clause. Here's the correct way to use ORDER BY:

-- Speeding ticket for when you forget the ORDER BY clause
SELECT DISTINCT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id
ORDER BY latest_order_date;


Here, order_date is aggregated, so it has a unique value per group that gives ORDER BY the green light.

Subqueries: Your SQL lifesavers

When dealing with complex queries using DISTINCT and ORDER BY, SQL's subqueries come to the rescue like superheroes. Structure your query so the subquery handles the DISTINCT operation, and the outer query applies the ORDER BY.

-- DOUBLE BURGER: DISTINCT in the subquery, ORDER BY in the outer query. Yum!
SELECT *
FROM (SELECT DISTINCT column1 FROM table_name) AS subquery
ORDER BY column1;


This makes SQL happy by sorting the subquery's DISTINCT column1, which is then ordered by the outer query.

Best practices with DISTINCT and ORDER BY

Here are ninja tricks to avoid pitfalls and ensure efficient SQL performance when working with DISTINCT and ORDER BY:

1. Aggregate Functions: Harness the power of aggregate functions like MAX(), MIN(), etc., for columns not part of the SELECT DISTINCT.
2. Similar Column Clause: Ensure the SELECT DISTINCT and ORDER BY clauses share the same columns. No confusion, no errors.
3. Subquery Superpower: Use subqueries to separate DISTINCT, thus achieving a cleaner and error-free code structure.

The story of a DISTINCT photography contest

Picturing a photography competition where each contestant (👤) submits a unique photo:

Contestants:      👤1 = [📸, 📸, 📷] 
                  👤2 = [📸, 📷, 📸]
                  👤3 = [📷, 📷, 📷]

Ah, the DISTINCT life:

🖼️ Gallery (DISTINCT):
- [📸, 📷]
# Only ONE instance of each unique photo is selected for the exhibit.

The Error: But when contestants' names are attached, "not a SELECTed expression" error occurs:

❌ Wrong Approach: 👤1's 📸, 👤2's 📷. 

✅ Correct Approach:
- Pair ALL details (👤+📸)
- DISTINCT with all SELECTed expressions.

Think of it as SQL ensuring that each distinct photo is appropriately paired with its contestant!

Useful references

1. SQL SELECT DISTINCT Statement — Master the DISTINCT keyword with SQL basics.
2. SELECT — Oracle's official word on the DISTINCT clause.
3. SQL DISTINCT | Intermediate SQL - Mode — Gain deeper understanding with SQL DISTINCT examples.
4. SQL | GROUP BY - GeeksforGeeks — Learn how GROUP BY and DISTINCT play differently.
5. Handling SQL DISTINCT Correctly in Complex Queries — Hone your skills with complex DISTINCT queries.