Oracle ORA-00924 Error: Causes and Solutions Complete Guide

ORA-00924: Missing BY Keyword in Oracle SQL

ORA-00924 is a syntax error thrown by the Oracle parser when it expects the keyword BY as part of a compound keyword clause — such as GROUP BY, ORDER BY, or PARTITION BY — but cannot find it. This error is caught at the parsing stage before any data is accessed, meaning your data is safe, but the query simply will not execute until the syntax is corrected. It is one of the most common beginner and intermediate-level SQL mistakes in Oracle environments.

---

Top 3 Causes

1. Missing BY in GROUP BY Clause

Omitting BY after GROUP is the single most frequent cause of ORA-00924. This often happens when developers are typing quickly or migrating SQL from other databases.

Incorrect:

-- ORA-00924: BY keyword missing after GROUP
SELECT department_id, COUNT(*) AS total
FROM employees
GROUP department_id;

Correct:

-- Fixed: GROUP BY written in full
SELECT department_id, COUNT(*) AS total
FROM employees
GROUP BY department_id;

---

2. Missing BY in ORDER BY Clause

Dropping BY from ORDER BY is another very common mistake, especially inside subqueries or inline views where the query structure gets complex.

Incorrect:

-- ORA-00924: BY keyword missing after ORDER
SELECT employee_id, last_name, salary
FROM employees
ORDER salary DESC;

Correct:

-- Fixed: ORDER BY written in full
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

---

3. Missing BY in Analytic Function's PARTITION BY or ORDER BY

Analytic (window) functions like RANK(), ROW_NUMBER(), and LAG() use OVER() clauses that require PARTITION BY and/or ORDER BY. Forgetting BY in either compound keyword triggers ORA-00924.

Incorrect:

-- ORA-00924: BY missing after PARTITION
SELECT employee_id,
       department_id,
       salary,
       RANK() OVER (PARTITION department_id ORDER BY salary DESC) AS rnk
FROM employees;

Correct:

-- Fixed: PARTITION BY and ORDER BY both written in full
SELECT employee_id,
       department_id,
       salary,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees
ORDER BY department_id, rnk;

---

Quick Fix Checklist

When you encounter ORA-00924, follow these steps:

1. Locate the line number — Oracle usually reports the approximate position of the error. Check the clause immediately before the unexpected token.
2. Search for incomplete compound keywords — Scan your query for standalone GROUP, ORDER, or PARTITION without the following BY.
3. Check analytic function OVER() clauses — Make sure every PARTITION and ORDER inside OVER(...) is followed by BY.
4. Use IDE autocomplete — Tools like SQL Developer, Toad, or DBeaver will auto-suggest GROUP BY and ORDER BY as full units, preventing partial keyword entry.

---

Prevention Tips

• Use SQL IDE snippets: Register standard templates such as GROUP BY, ORDER BY, and PARTITION BY [col] ORDER BY [col] as code snippets in your editor. This eliminates the risk of manually mistyping compound keywords.
• Enable syntax highlighting and linting: Modern SQL IDEs highlight keywords in distinct colors. If GROUP appears without BY, it will either not be highlighted correctly or flagged by a linter — giving you instant visual feedback before you even run the query.

---

Related Oracle Errors

Error Code	Description
ORA-00923	FROM keyword not found where expected
ORA-00933	SQL command not properly ended
ORA-00936	missing expression
ORA-00907	missing right parenthesis

These errors often appear alongside ORA-00924 when SQL syntax issues cascade through a complex query. Fixing ORA-00924 first is recommended, as correcting the BY keyword may resolve adjacent parsing errors automatically.

---

📖 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.