Ora-01791: not a SELECTed expression

sql

join

best-practices

performance

byAlex Kataev·Mar 1, 2025

To fix ORA-01791: include all ORDER BY fields in the SELECT DISTINCT query. To keep the order by fields that you didn't SELECT DISTINCT, wrap your query as follows:

An amended query:

SELECT DISTINCT name FROM users ORDER BY name;

Or, retain the original order without choosing id:

SELECT name FROM (
  SELECT DISTINCT name, id FROM users
) ORDER BY id;

Causes of ORA-01791 error and how to fix it

The turning point: the ORA-01791 error will knock on your door when you try to ORDER BY a column that has not been invited to the SELECT DISTINCT party. The fix? Either invite everyone to the party or change the guest list!

Understanding ORA-01791: Examples & Solutions

Scenario 1: Mixing COUNT with DISTINCT is like pineapple on pizza - it's a matter of taste. However, if the grouping isn't correct, you may get the ORA-01791 error served on your plate.

-- Correct way to mix 'COUNT' with 'DISTINCT':
-- It's like getting the correct pineapple to cheese ratio on your pizza 🍍🍕
SELECT status, COUNT(DISTINCT id)
FROM orders
GROUP BY status
ORDER BY status;

Scenario 2: Columns not prettied up with an aggregate function must dress up with the GROUP BY clause for the SELECT DISTINCT party.

-- Improperly dressed columns are a no-go!
-- This is the 'black tie event' of SQL
SELECT customer_id, MAX(order_amount)
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Essential Tips for writing SELECT DISTINCT Queries to Avoid ORA-01791

Consistency between SELECT and ORDER BY

Keep SELECT and ORDER BY on the same page. An aligned understanding between them is the key to prevent errors.

-- When 'SELECT' and 'ORDER BY' are best buddies 
SELECT DISTINCT status, customer_id
FROM orders
ORDER BY status, customer_id;

Revising the STRUCTURE of your SQL Query

Sometimes it's not about just correcting errors - it's about rethinking your query's logic.

-- Sometimes, a query must take a step back before it takes two steps forward
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY category;

How to Handle ALIASES and JOINS

When using aliases, ensure that they don't blur the bond between SELECT and ORDER BY.

-- Aliases can be tricky, handle them with care
SELECT category AS cat, COUNT(*) AS total
FROM products
GROUP BY category
ORDER BY cat;

When dealing with joins, it's crucial that aggregated columns are not wrongly referenced from different tables.

-- Be careful when using joins with aggregated columns
-- It's like trying to make two stubborn cats befriend each other 🐈‍⬛🐈
SELECT o.customer_id, SUM(i.quantity) AS total_quantity
FROM orders o, ORDER_ITEMS i
WHERE o.id = i.order_id
GROUP BY o.customer_id
ORDER BY total_quantity;