Home / SQL / ORDER BY

ORDER BY

Overview

The ORDER BY clause is used to sort the result set in ascending or descending order. By default, ORDER BY sorts records in ascending order (ASC).

ORDER BY allows you to organize query results in a meaningful way, making data easier to read and analyze.

Basic Syntax

The ORDER BY clause comes after WHERE (if present):

ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Sorting in Ascending Order (ASC)

Ascending order (A-Z, 0-9) is the default. You can explicitly use ASC:

Ascending Sort (Default)

-- Both queries are equivalent
SELECT name, price
FROM Products
ORDER BY price ASC;

SELECT name, price
FROM Products
ORDER BY price;

Sorting in Descending Order (DESC)

Use DESC to sort in descending order (Z-A, 9-0):

Descending Sort

SELECT name, price
FROM Products
ORDER BY price DESC;

Examples with Sample Data

Let's see ORDER BY in action:

Sample Products Table

Products Table:
┌────────────┬──────────────┬───────┐
│ product_id │ product_name │ price │
├────────────┼──────────────┼───────┤
│ 1          │ Laptop       │ 999   │
│ 2          │ Mouse        │ 25    │
│ 3          │ Keyboard     │ 75    │
│ 4          │ Monitor      │ 299   │
│ 5          │ Headphones   │ 150   │
└────────────┴──────────────┴───────┘

Example 1: Sort by Price (Ascending)

Query

SELECT product_name, price
FROM Products
ORDER BY price ASC;

Result

┌──────────────┬───────┐
│ product_name │ price │
├──────────────┼───────┤
│ Mouse        │ 25    │
│ Keyboard     │ 75    │
│ Headphones   │ 150   │
│ Monitor      │ 299   │
│ Laptop       │ 999   │
└──────────────┴───────┘

Example 2: Sort by Price (Descending)

Query

SELECT product_name, price
FROM Products
ORDER BY price DESC;

Result

┌──────────────┬───────┐
│ product_name │ price │
├──────────────┼───────┤
│ Laptop       │ 999   │
│ Monitor      │ 299   │
│ Headphones   │ 150   │
│ Keyboard     │ 75    │
│ Mouse        │ 25    │
└──────────────┴───────┘

Sorting by Multiple Columns

You can sort by multiple columns. The first column is sorted first, then rows with the same value in the first column are sorted by the second column:

Multiple Column Sorting

SELECT first_name, last_name, salary
FROM Employees
ORDER BY department ASC, salary DESC;

This sorts employees first by department (A-Z), then within each department, sorts by salary (highest to lowest).

Sorting by Column Position

You can also sort by the column position in the SELECT list (1 = first column, 2 = second column, etc.):

Sort by Column Position

-- Sort by the 2nd column (price)
SELECT product_name, price, category
FROM Products
ORDER BY 2 DESC;

-- Note: It's generally better to use column names for clarity

Sorting Text Data

Text sorting follows alphabetical order:

Sort Text Alphabetically

-- Ascending: A to Z
SELECT name, email
FROM Customers
ORDER BY name ASC;

-- Descending: Z to A
SELECT name, email
FROM Customers
ORDER BY name DESC;

Sorting Date and Time Data

Dates and times sort chronologically:

Sort by Date

-- Most recent first
SELECT order_id, order_date, total_amount
FROM Orders
ORDER BY order_date DESC;

-- Oldest first
SELECT order_id, order_date, total_amount
FROM Orders
ORDER BY order_date ASC;

ORDER BY with WHERE

You can combine ORDER BY with WHERE to filter and sort:

Filter and Sort

SELECT product_name, price
FROM Products
WHERE category = 'Electronics'
ORDER BY price DESC;

ORDER BY with JOIN

When using JOINs, you can sort by columns from any joined table:

Sort Joined Results

SELECT c.name, o.order_date, o.total_amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC, o.total_amount DESC;

ORDER BY with Aggregate Functions

You can sort by aggregate function results (with GROUP BY):

Sort by Aggregated Values

SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM Products
GROUP BY category
ORDER BY product_count DESC;

NULL Values in ORDER BY

NULL values are typically sorted first in ascending order and last in descending order (behavior may vary by database):

NULL Handling

-- NULLs typically appear first with ASC
SELECT name, email
FROM Customers
ORDER BY email ASC;

-- NULLs typically appear last with DESC
SELECT name, email
FROM Customers
ORDER BY email DESC;

-- Explicit NULL handling (database-specific)
SELECT name, email
FROM Customers
ORDER BY email NULLS LAST; -- PostgreSQL syntax

Performance Considerations

Use indexes: Indexes on ORDER BY columns can significantly improve performance
Avoid unnecessary sorting: Only sort when needed
Limit results first: Use WHERE to reduce data before sorting when possible
Consider TOP/LIMIT: Use LIMIT or TOP to get only needed sorted rows

Common Use Cases

Displaying products by price (lowest/highest first)
Listing customers alphabetically
Showing most recent orders first
Ranking by sales or performance metrics
Organizing data for reports and dashboards

Best Practices

Always use ORDER BY: For consistent result ordering, especially in applications
Use column names: More readable than column positions
Combine with LIMIT: When you only need top N results
Consider indexes: For frequently sorted columns, create indexes
Document sort order: Make sorting logic clear in your code

Next Steps

Learn about combining multiple conditions with AND, OR, NOT, or explore filtering techniques with WHERE clause.

← Where And Or Not →