How to Use ORDER BY in Oracle Database 23ai Queries

The ORDER BY clause in Oracle Database 23ai lets you sort the rows returned by a query. Without it, the database can return results in any order. ORDER BY helps you control whether the rows are listed alphabetically, numerically, chronologically, or even by similarity when working with vectors. This tutorial explains how ORDER BY works in Oracle Database 23ai, shows examples with traditional data types, JSON fields, and new VECTOR data type, and provides clear result sets to make everything easy to follow.

Preparing sample data

We will use the same tables (employees, departments, products, and documents) so you can practice ORDER BY queries step by step.

-- Drop old tables if they exist
DROP TABLE employees PURGE;
DROP TABLE departments PURGE;
DROP TABLE products PURGE;
DROP TABLE documents PURGE;

-- Employees
CREATE TABLE employees (
  employee_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10,2),
  department_id NUMBER
);

INSERT INTO employees VALUES (1,'John','Smith',   DATE '2020-01-15', 8000, 10);
INSERT INTO employees VALUES (2,'Alice','Johnson',DATE '2019-03-10',12000, 20);
INSERT INTO employees VALUES (3,'Michael','Brown',DATE '2021-06-20', 4500, 10);
INSERT INTO employees VALUES (4,'Sophia','Davis', DATE '2022-11-05', 6000, 30);
INSERT INTO employees VALUES (5,'David','Wilson', DATE '2021-02-18', 9500, 20);

-- Departments
CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100)
);
INSERT INTO departments VALUES (10,'Sales');
INSERT INTO departments VALUES (20,'HR');
INSERT INTO departments VALUES (30,'IT');

-- Products with JSON
CREATE TABLE products (
  product_id   NUMBER PRIMARY KEY,
  product_data JSON
);
INSERT INTO products VALUES (1,'{"name":"Laptop","specs":{"cpu":"Intel i7","ram":"16GB"},"price":1299}');
INSERT INTO products VALUES (2,'{"name":"Phone","specs":{"cpu":"Snapdragon","ram":"8GB"},"price":699}');
INSERT INTO products VALUES (3,'{"name":"Tablet","specs":{"cpu":"M2","ram":"12GB"},"price":899}');
INSERT INTO products VALUES (4,'{"name":"Laptop","specs":{"cpu":"Intel i5","ram":"8GB"},"price":999}');

-- Documents with VECTOR
CREATE TABLE documents (
  doc_id    NUMBER PRIMARY KEY,
  title     VARCHAR2(200),
  embedding VECTOR
);
INSERT INTO documents VALUES (1,'Intro to 23ai','[0.10,0.80,0.50]');
INSERT INTO documents VALUES (2,'Write SQL Fast','[0.20,0.70,0.60]');
INSERT INTO documents VALUES (3,'AI and Databases','[0.85,0.15,0.20]');

COMMIT;

Now the data is ready for ORDER BY queries.

Basic ORDER BY with one column

The most common use is to sort by a single column in ascending order (default).

SELECT first_name, salary
FROM employees
ORDER BY salary;

Result

ORDER BY descending

You can reverse the order using the keyword DESC.

SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

Result

ORDER BY multiple columns

When sorting by multiple columns, Oracle applies the order from left to right.

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

Result

ORDER BY position

Instead of writing column names, you can use column positions in the SELECT list. This is not recommended for readability but works.

SELECT first_name, salary
FROM employees
ORDER BY 2 DESC;

This sorts results by the second column (salary).

ORDER BY with expressions

You can also sort by calculated expressions.

SELECT first_name, salary, salary*12 AS annual_salary
FROM employees
ORDER BY salary*12 DESC;

Result

ORDER BY with dates

You can sort date values chronologically.

SELECT first_name, hire_date
FROM employees
ORDER BY hire_date;

Result

ORDER BY with NULL values

By default, NULL values appear last in ascending order and first in descending order. You can control this with NULLS FIRST or NULLS LAST.

SELECT first_name, commission_pct
FROM employees
ORDER BY commission_pct NULLS LAST;

Result

ORDER BY JSON values (23ai)

Oracle 23ai lets you query JSON fields directly and use them in ORDER BY.

SELECT JSON_VALUE(product_data,'$.name') AS name,
       JSON_VALUE(product_data,'$.price') AS price
FROM products
ORDER BY JSON_VALUE(product_data,'$.price') DESC;

Result

ORDER BY with VECTOR similarity (23ai)

When working with embeddings, ORDER BY can rank results by vector distance.

SELECT doc_id, title
FROM documents
ORDER BY VECTOR_DISTANCE(embedding,'[0.18,0.72,0.58]', COSINE)
FETCH FIRST 2 ROWS ONLY;

Result

This example finds the two documents most similar to the query vector.

Best practices for ORDER BY

1. Always specify ASC or DESC explicitly for clarity.
2. Use NULLS FIRST or NULLS LAST when working with missing values.
3. Prefer column names instead of positions for readability.
4. When sorting JSON values, ensure the path exists.
5. For vector data, ORDER BY VECTOR_DISTANCE is the key to semantic ranking.

Conclusion

The ORDER BY clause in Oracle Database 23ai is essential for controlling the order of your query results. You can sort by numbers, text, dates, NULLs, expressions, JSON fields, and even similarity scores from vector embeddings. With these features, ORDER BY is not just about neat presentation but also about enabling advanced AI-driven search and analysis.

Note: All examples in this tutorial were tested on Oracle Database 23ai using Oracle SQL Developer version 24.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows