Oracle ORA-01791 Error: Causes and Solutions Complete Guide

ORA-01791: not a SELECTed expression — Causes, Fixes & Prevention

ORA-01791 is thrown by Oracle when a column or expression referenced in the ORDER BY clause does not appear in the SELECT list. This most commonly occurs when using DISTINCT, UNION, INTERSECT, or MINUS operators, where Oracle strictly enforces that sort keys must be part of the projected result set. Understanding when and why Oracle imposes this restriction is key to writing robust SQL.

---

Top 3 Causes

1. Using ORDER BY with a Non-Selected Column After DISTINCT

When SELECT DISTINCT is used, Oracle eliminates duplicates based solely on the columns listed in the SELECT clause. If ORDER BY references a column not in that list, Oracle cannot resolve its value in the deduplicated result set.

-- Causes ORA-01791
SELECT DISTINCT department_id, first_name
FROM employees
ORDER BY salary;  -- salary is not in the SELECT list

-- Fix: Add salary to the SELECT list
SELECT DISTINCT department_id, first_name, salary
FROM employees
ORDER BY salary;

-- Alternative Fix: Use GROUP BY instead of DISTINCT for more flexibility
SELECT department_id, first_name, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id, first_name
ORDER BY max_salary DESC;

---

2. Referencing a Non-First-SELECT Column in UNION / INTERSECT / MINUS Queries

In compound queries using set operators, Oracle bases ORDER BY resolution on the column list of the first SELECT statement. Any column name that does not appear in the first SELECT — even if it exists in subsequent ones — will trigger ORA-01791.

-- Causes ORA-01791
SELECT employee_id, first_name FROM employees
UNION
SELECT department_id, department_name FROM departments
ORDER BY department_name;  -- Not in the first SELECT

-- Fix 1: Use positional (column index) notation
SELECT employee_id, first_name FROM employees
UNION
SELECT department_id, department_name FROM departments
ORDER BY 2;  -- Refers to the 2nd column in the result set

-- Fix 2: Use an alias from the first SELECT
SELECT employee_id AS id, first_name AS name FROM employees
UNION
SELECT department_id, department_name FROM departments
ORDER BY name;  -- 'name' alias is defined in the first SELECT

---

3. ORDER BY Referencing a Column Not Exposed by an Inline View

When you wrap a query in an inline view (subquery in the FROM clause), only the columns projected by that view are visible to the outer query. Ordering by a column that exists inside the inline view but is not included in its SELECT list causes ORA-01791.

-- Causes ORA-01791 (salary not in outer SELECT)
SELECT emp_name
FROM (
    SELECT first_name AS emp_name, salary
    FROM employees
    WHERE department_id = 10
)
ORDER BY salary;  -- May fail if salary is not surfaced properly

-- Fix: Expose salary in both the inline view and the outer SELECT
SELECT emp_name, salary
FROM (
    SELECT first_name AS emp_name, salary
    FROM employees
    WHERE department_id = 10
)
ORDER BY salary;

-- Practical pattern: DISTINCT + ORDER BY using a subquery wrapper
SELECT emp_name, dept_id
FROM (
    SELECT DISTINCT
        first_name     AS emp_name,
        department_id  AS dept_id,
        salary
    FROM employees
)
ORDER BY salary DESC;

---

Quick Fix Summary

Scenario	Fix
DISTINCT + non-listed ORDER BY column	Add the column to the SELECT list
UNION with non-first-SELECT column	Use positional index or alias from first SELECT
Inline view missing column	Expose the column in the subquery's SELECT

---

Prevention Tips

Always verify ORDER BY columns exist in SELECT before running compound queries.
Before executing any query using DISTINCT or set operators, quickly scan your ORDER BY clause and confirm every referenced column or alias is present in the SELECT list. Using SQL editors like SQL Developer or Toad with real-time syntax checking helps catch this before runtime.

Standardize the use of column aliases and positional notation in UNION-based queries.
Establish a team coding standard that mandates clear column aliases in the first SELECT of any compound query, and always use those aliases — or positional numbers — in the ORDER BY clause. This eliminates ambiguity, prevents ORA-01791, and makes complex set-operation queries far easier to maintain and review.

---

Related Oracle Errors

• ORA-00904 – Invalid identifier; often confused with ORA-01791 when a typo exists in an ORDER BY column name.
• ORA-01785 – ORDER BY item must be a number or expression in a UNION query; closely related to ORA-01791 in compound query contexts.
• ORA-00923 – Missing FROM keyword; can co-occur when the SELECT clause is malformed alongside ORDER BY issues.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.