SQL Order By | Academic Block

SQL ORDER BY

Learn how to sort SQL query results in ascending or descending order using the ORDER BY clause.

What is the ORDER BY Clause?

The ORDER BY clause is used to sort the rows returned by a SQL query. You can sort data by one or more columns in ascending or descending order.

By default, SQL sorts values in ascending order when no direction is specified.

Basic ORDER BY Syntax

The basic syntax places ORDER BY after the SELECT and FROM clauses.

WITH employees(name, salary) AS (
  VALUES
    ('Aarav', 65000),
    ('Meera', 72000),
    ('Rohan', 58000)
)
SELECT name, salary
FROM employees
ORDER BY salary;

ORDER BY ASC

The ASC keyword sorts values in ascending order. This is the default sorting direction.

WITH products(name, price) AS (
  VALUES
    ('Monitor', 1500),
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Speaker', 2200)
)
SELECT name, price
FROM products
ORDER BY price ASC;

ORDER BY DESC

The DESC keyword sorts values in descending order.

WITH products(name, price) AS (
  VALUES
    ('Monitor', 1500),
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Speaker', 2200)
)
SELECT name, price
FROM products
ORDER BY price DESC;

Sorting Text Values

ORDER BY can also sort text values alphabetically.

WITH students(name) AS (
  VALUES
    ('Rohan'),
    ('Aman'),
    ('Meera'),
    ('Zoya'),
    ('Anika')
)
SELECT name
FROM students
ORDER BY name ASC;

Sorting Text in Descending Order

Adding DESC reverses the alphabetical ordering.

WITH students(name) AS (
  VALUES
    ('Rohan'),
    ('Aman'),
    ('Meera'),
    ('Zoya'),
    ('Anika')
)
SELECT name
FROM students
ORDER BY name DESC;

Sorting by Multiple Columns

You can sort by multiple columns by separating the column names with commas. SQL first sorts by the first column, then uses the next column when values are equal.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'IT', 72000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'Finance', 65000)
)
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Different Sort Directions

Each column in an ORDER BY clause can have its own sorting direction.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'IT', 72000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'Finance', 65000)
)
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

ORDER BY with WHERE

WHERE filters rows first, and ORDER BY then sorts the remaining rows.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'IT', 58000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'Finance', 55000)
)
SELECT name, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;

ORDER BY with DISTINCT

ORDER BY can be used with DISTINCT to sort the unique values returned by the query.

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', 'Finance'),
    ('Rohan', 'IT'),
    ('Anika', 'HR'),
    ('Vikram', 'Finance')
)
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

ORDER BY with Calculated Columns

You can sort query results using a calculated expression.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3),
    ('Monitor', 1500, 1),
    ('Speaker', 2200, 2)
)
SELECT name,
       price,
       quantity,
       price * quantity AS total_cost
FROM products
ORDER BY total_cost DESC;

ORDER BY with Column Alias

In SQLite, a column alias created in the SELECT list can be used in the ORDER BY clause.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3),
    ('Monitor', 1500, 1)
)
SELECT name,
       price * quantity AS total_price
FROM products
ORDER BY total_price DESC;

ORDER BY with Aggregate Functions

Aggregate results can also be sorted using ORDER BY.

WITH sales(department, amount) AS (
  VALUES
    ('IT', 1200),
    ('IT', 1800),
    ('HR', 900),
    ('HR', 1100),
    ('Sales', 2500)
)
SELECT department,
       SUM(amount) AS total_sales
FROM sales
GROUP BY department
ORDER BY total_sales DESC;

ORDER BY with GROUP BY

After grouping rows with GROUP BY, ORDER BY can sort the grouped results.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'IT', 72000),
    ('Rohan', 'HR', 58000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'Sales', 70000)
)
SELECT department,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC;

ORDER BY with LIMIT

In SQLite, ORDER BY can be combined with LIMIT to retrieve the top or bottom rows after sorting.

WITH employees(name, salary) AS (
  VALUES
    ('Aarav', 65000),
    ('Meera', 72000),
    ('Rohan', 58000),
    ('Anika', 62000),
    ('Vikram', 80000)
)
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Sorting NULL Values

SQLite allows NULL values in columns. When sorting, the position of NULL values depends on the sort direction.

WITH employees(name, bonus) AS (
  VALUES
    ('Aarav', 5000),
    ('Meera', NULL),
    ('Rohan', 3000),
    ('Anika', NULL),
    ('Vikram', 7000)
)
SELECT name, bonus
FROM employees
ORDER BY bonus ASC;

Sorting by Column Position

SQLite also allows you to refer to selected columns by their position in the SELECT list. For example, ORDER BY 2 sorts by the second selected column.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500)
)
SELECT name, price
FROM products
ORDER BY 2 DESC;

ORDER BY with CASE

The CASE expression can be used inside ORDER BY to create custom sorting rules.

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', 'HR'),
    ('Rohan', 'Finance'),
    ('Anika', 'IT'),
    ('Vikram', 'Sales')
)
SELECT name, department
FROM employees
ORDER BY CASE department
           WHEN 'IT' THEN 1
           WHEN 'Finance' THEN 2
           WHEN 'HR' THEN 3
           WHEN 'Sales' THEN 4
           ELSE 5
         END;

Common ORDER BY Examples

Syntax	Purpose	Result
`ORDER BY price`	Sort by price	Ascending
`ORDER BY price ASC`	Explicit ascending sort	Lowest to highest
`ORDER BY price DESC`	Descending sort	Highest to lowest
`ORDER BY department, salary DESC`	Sort using multiple columns	Department first, salary second
`ORDER BY total_price DESC`	Sort using an alias	Highest calculated value first

Order of Clauses in a SELECT Query

ORDER BY normally appears after the filtering, grouping, and aggregation clauses in a SELECT query.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'IT', 72000),
    ('Rohan', 'HR', 58000),
    ('Anika', 'HR', 62000),
    ('Vikram', 'Sales', 70000)
)
SELECT department,
       AVG(salary) AS average_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY average_salary DESC
LIMIT 5;

Advantages of ORDER BY

Organizes query results in a predictable order.
Makes numerical data easier to compare.
Makes text results easier to browse alphabetically.
Allows multiple sorting criteria.
Can be combined with LIMIT to retrieve the highest or lowest rows.
Can sort calculated and aggregate results.

Best Practices

Always specify ASC or DESC when the desired direction needs to be obvious.
Use multiple columns when a single column does not provide enough sorting detail.
Use column names or aliases instead of positional references when readability is important.
Combine ORDER BY with LIMIT when selecting the highest or lowest rows.
Remember that SQL does not guarantee a particular result order unless ORDER BY is specified.

The ORDER BY clause gives you control over how SQL query results are presented. By combining ascending and descending sorting, multiple columns, calculated values, and LIMIT, you can organize and retrieve exactly the results you need.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.