When working with databases, retrieving the correct data is only part of the job. In many applications, you also need to display that data in a specific order.

For example:

• Show products from lowest price to highest.
• Display employees with the highest salaries first.
• Show the newest orders first.
• Sort customers alphabetically.
• Display the most popular products at the top.
• Sort results using multiple columns.

This is where the SQL ORDER BY clause is used.

The ORDER BY clause allows you to sort the rows returned by a SQL query in ascending or descending order.

In this complete guide, you will learn what ORDER BY is, its syntax, ASC and DESC, sorting text and numbers, multiple columns, dates, NULL values, aliases, expressions, GROUP BY, JOIN, LIMIT, pagination, common mistakes, performance considerations, and practical real-world examples.

---

What Is SQL ORDER BY?

The SQL ORDER BY clause is used to sort the result of a query based on one or more columns or expressions.

For example:

SELECT *
FROM products
ORDER BY price;

By default, SQL sorts the results in ascending order.

If the products have prices:

product	price
Laptop	70000
Mouse	800
Keyboard	1500
Monitor	12000

The query can return:

product	price
Mouse	800
Keyboard	1500
Monitor	12000
Laptop	70000

---

Why Is ORDER BY Important?

Databases do not guarantee that rows will be returned in a particular order unless you explicitly specify an ORDER BY clause.

For example:

SELECT *
FROM products;

should not be assumed to return products in ID order, insertion order, alphabetical order, or any other specific order.

If you need a predictable order, use:

SELECT *
FROM products
ORDER BY product_name;

This is especially important for:

• User interfaces
• Reports
• Admin dashboards
• Search results
• Product listings
• Leaderboards
• Pagination
• Financial reports
• Data exports

---

SQL ORDER BY Syntax

The basic syntax is:

SELECT column1, column2
FROM table_name
ORDER BY column_name;

For descending order:

SELECT column1, column2
FROM table_name
ORDER BY column_name DESC;

You can also explicitly specify ascending order:

SELECT column1, column2
FROM table_name
ORDER BY column_name ASC;

---

ASC vs DESC

SQL provides two main sorting directions.

ASC

ASC means ascending.

For numbers:

1 → 2 → 3 → 4 → 5

For alphabetical text, roughly:

A → B → C → D

Example:

SELECT *
FROM employees
ORDER BY salary ASC;

This shows the lowest salaries first.

---

DESC

DESC means descending.

For numbers:

5 → 4 → 3 → 2 → 1

Example:

SELECT *
FROM employees
ORDER BY salary DESC;

This shows the highest salaries first.

---

ASC Is the Default

If you don’t specify ASC or DESC, ascending order is normally assumed.

These two queries are equivalent:

SELECT *
FROM products
ORDER BY price;

and:

SELECT *
FROM products
ORDER BY price ASC;

Using ASC explicitly can sometimes make your intention clearer.

---

Sorting Numeric Values

You can sort numeric columns using ORDER BY.

For example:

SELECT *
FROM products
ORDER BY price ASC;

This displays products from the cheapest to the most expensive.

To show the most expensive products first:

SELECT *
FROM products
ORDER BY price DESC;

---

Sorting Text Values

ORDER BY can also sort text columns.

Example:

SELECT *
FROM customers
ORDER BY name ASC;

This generally sorts names alphabetically according to the database’s collation rules.

For reverse alphabetical order:

SELECT *
FROM customers
ORDER BY name DESC;

Important

Text sorting can vary depending on:

• Database system
• Collation
• Character set
• Locale
• Case-sensitivity rules
• Accent handling

Therefore, don’t assume that every database sorts text exactly the same way.

---

Sorting Dates

Dates can also be sorted.

For example:

SELECT *
FROM orders
ORDER BY order_date ASC;

This displays older orders first.

To show the newest orders first:

SELECT *
FROM orders
ORDER BY order_date DESC;

This is extremely common in applications.

For example, an admin dashboard may show the latest orders first:

SELECT order_id, customer_id, order_date, amount
FROM orders
ORDER BY order_date DESC;

---

Sorting by Multiple Columns

You can sort by more than one column.

Syntax:

ORDER BY column1 ASC, column2 DESC;

For example:

SELECT *
FROM employees
ORDER BY department ASC, salary DESC;

The database first sorts employees by department.

Within each department, employees are sorted by salary from highest to lowest.

Example

department	name	salary
HR	Priya	70000
HR	Neha	50000
IT	Rahul	90000
IT	Amit	65000
Sales	Ravi	60000

---

How Multiple-Column ORDER BY Works

Suppose you have:

city	name	age
Jaipur	Rahul	30
Delhi	Amit	25
Jaipur	Amit	22
Delhi	Neha	35

Query:

SELECT *
FROM customers
ORDER BY city ASC, age DESC;

SQL first sorts by city.

Then, for customers with the same city, it sorts by age in descending order.

The important concept is:

SQL uses the next ORDER BY expression to break ties from the previous expression.

---

Different Sort Directions for Different Columns

You can specify different directions for each column.

SELECT *
FROM employees
ORDER BY department ASC, salary DESC, name ASC;

This means:

1. Department: A → Z
2. Salary within each department: highest → lowest
3. Name for employees with the same department and salary: A → Z

This is very useful for reports.

---

ORDER BY with SELECT

The most basic use is with SELECT.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

This returns employees from the highest salary to the lowest.

---

ORDER BY with WHERE

You can combine WHERE and ORDER BY.

SELECT *
FROM products
WHERE price > 1000
ORDER BY price DESC;

Conceptually:

1. Filter products whose price is greater than 1,000.
2. Sort the matching products from highest price to lowest.

---

ORDER BY with GROUP BY

ORDER BY can be used with grouped results.

For example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department
ORDER BY total_employees DESC;

This displays departments with the largest number of employees first.

Another example:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

This ranks customers according to their total spending.

---

ORDER BY with HAVING

You can use WHERE, GROUP BY, HAVING, and ORDER BY together.

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY average_salary DESC;

Conceptually:

1. Select active employees.
2. Group them by department.
3. Calculate average salary.
4. Keep departments with an average above 50,000.
5. Sort the remaining departments from highest average salary to lowest.

---

ORDER BY with JOIN

ORDER BY can sort data after joining multiple tables.

Example:

SELECT
    c.name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY o.amount DESC;

This displays orders from highest amount to lowest.

You can also sort by customer name:

SELECT
    c.name,
    o.order_id,
    o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.name ASC;

---

ORDER BY with Table Aliases

Aliases make queries easier to read.

SELECT
    c.name,
    o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY o.amount DESC;

Here:

• c represents customers
• o represents orders

Using aliases is especially helpful when multiple tables contain columns with the same name.

---

ORDER BY Using Column Aliases

You can often sort by an alias defined in the SELECT list.

For example:

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

The alias annual_salary represents:

salary × 12

This is convenient for calculated values.

Database behavior around aliases can vary in certain query contexts, so check your database if a query uses more advanced expressions.

---

ORDER BY Using Column Position

Some SQL systems allow you to order by the position of a selected column.

For example:

SELECT name, salary
FROM employees
ORDER BY 2 DESC;

Here, 2 refers to the second selected column, which is salary.

Although this can work, it is generally less readable than:

SELECT name, salary
FROM employees
ORDER BY salary DESC;

For maintainable production SQL, explicitly naming the column is usually preferable.

---

ORDER BY Using Expressions

You can sort using calculations or expressions.

For example:

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

You can also use expressions such as:

SELECT *
FROM products
ORDER BY price * quantity DESC;

This can rank products according to calculated value.

---

ORDER BY with CASE

CASE can be used when you need a custom sorting order.

Suppose you have order statuses:

• Pending
• Processing
• Shipped
• Completed
• Cancelled

You might want them displayed in a business-specific order rather than alphabetically.

For example:

SELECT *
FROM orders
ORDER BY CASE status
    WHEN 'Pending' THEN 1
    WHEN 'Processing' THEN 2
    WHEN 'Shipped' THEN 3
    WHEN 'Completed' THEN 4
    WHEN 'Cancelled' THEN 5
    ELSE 6
END;

This allows you to define your own priority.

---

ORDER BY with NULL Values

NULL represents a missing or unknown value.

When sorting, the position of NULL values depends on the database system and sort direction.

For example:

SELECT *
FROM customers
ORDER BY phone ASC;

If some customers have NULL phone numbers, their position may differ between database systems.

Some databases allow explicit control using:

ORDER BY phone ASC NULLS LAST;

or:

ORDER BY phone DESC NULLS FIRST;

However, NULLS FIRST and NULLS LAST are not supported identically across all database systems.

For portable applications, use database-specific syntax or an expression when explicit NULL placement is required.

---

ORDER BY with LIMIT

ORDER BY is commonly combined with LIMIT to find the top or bottom records.

For example, to find the five most expensive products:

SELECT *
FROM products
ORDER BY price DESC
LIMIT 5;

The important order is:

Sort → Limit

If you want the five cheapest products:

SELECT *
FROM products
ORDER BY price ASC
LIMIT 5;

LIMIT is supported by systems such as MySQL and PostgreSQL. Other databases use different syntax.

---

Finding the Highest Salary

You can find the highest salary using:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 1;

Alternatively, you could use:

SELECT MAX(salary)
FROM employees;

These queries answer slightly different questions.

MAX() returns the highest value.

ORDER BY ... LIMIT 1 can return the complete row associated with the highest value, although ties require additional consideration.

---

Handling Ties

Suppose two employees have the same highest salary.

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 1;

This returns only one row.

If you want a deterministic winner based on another column, add a tie-breaker:

SELECT *
FROM employees
ORDER BY salary DESC, employee_id ASC
LIMIT 1;

If you want all employees tied for the highest salary, use another approach, such as:

SELECT *
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

This returns every employee whose salary equals the maximum salary.

---

ORDER BY for Pagination

ORDER BY is extremely important when implementing pagination.

A simple approach might be:

SELECT *
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;

This retrieves a page of results after skipping 40 rows.

However, offset-based pagination can become inefficient for very large datasets.

A more scalable approach is often keyset pagination.

For example:

SELECT *
FROM products
WHERE product_id > 40
ORDER BY product_id
LIMIT 20;

This approach can perform better for large datasets when the ordering column is appropriately indexed.

The exact pagination strategy should depend on your application’s requirements and database.

---

ORDER BY for Latest Records

A common requirement is to display the newest records first.

SELECT *
FROM posts
ORDER BY created_at DESC;

This is frequently used for:

• Blog posts
• Notifications
• Orders
• Messages
• Transactions
• User activity
• News feeds

---

ORDER BY for Oldest Records

To display the oldest records first:

SELECT *
FROM posts
ORDER BY created_at ASC;

---

ORDER BY Randomly

Some databases provide a function for random ordering.

For example, in MySQL:

SELECT *
FROM products
ORDER BY RAND();

In PostgreSQL:

SELECT *
FROM products
ORDER BY RANDOM();

This can be useful for small datasets, but random sorting can be expensive for large tables because the database may need to calculate a random value for many rows.

For large-scale systems, use a more efficient random-selection strategy when appropriate.

---

ORDER BY with DISTINCT

You can combine DISTINCT and ORDER BY.

For example:

SELECT DISTINCT city
FROM customers
ORDER BY city ASC;

This returns unique cities in alphabetical order.

Be aware that database-specific rules can restrict what you can put in ORDER BY when DISTINCT is used.

---

ORDER BY with UNION

ORDER BY can be used with a combined query.

For example:

SELECT name
FROM customers

UNION

SELECT name
FROM employees

ORDER BY name ASC;

The final ORDER BY sorts the combined result.

When using UNION, ORDER BY generally applies to the final result rather than independently sorting each component query.

---

ORDER BY and SQL Execution Order

A simplified conceptual order of SQL processing is:

FROM
  ↓
JOIN
  ↓
WHERE
  ↓
GROUP BY
  ↓
HAVING
  ↓
SELECT
  ↓
DISTINCT
  ↓
ORDER BY
  ↓
LIMIT / OFFSET

For example:

SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY total_employees DESC
LIMIT 10;

Conceptually:

1. Read employees.
2. Filter salaries above 50,000.
3. Group employees by department.
4. Remove groups with five or fewer employees.
5. Produce the selected result.
6. Sort by employee count.
7. Return the first 10 rows.

The database optimizer may physically execute operations in a different order while preserving the query’s logical result.

---

ORDER BY and GROUP BY Difference

These clauses have different purposes.

GROUP BY

Groups rows together:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

ORDER BY

Sorts the result:

SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
ORDER BY total DESC;

A simple way to remember:

GROUP BY creates groups. ORDER BY sorts results.

---

ORDER BY vs WHERE

WHERE filters rows.

ORDER BY sorts rows.

Example:

SELECT *
FROM products
WHERE price > 1000
ORDER BY price DESC;

Here:

• WHERE price > 1000 removes products priced at 1,000 or less.
• ORDER BY price DESC sorts the remaining products from highest to lowest price.

---

ORDER BY vs HAVING

HAVING filters groups after aggregation.

ORDER BY sorts the final result.

Example:

SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY average_salary DESC;

---

Practical SQL ORDER BY Examples

Example 1: Sort Products by Price

SELECT *
FROM products
ORDER BY price ASC;

---

Example 2: Show Expensive Products First

SELECT *
FROM products
ORDER BY price DESC;

---

Example 3: Sort Customers Alphabetically

SELECT *
FROM customers
ORDER BY name ASC;

---

Example 4: Show Newest Orders First

SELECT *
FROM orders
ORDER BY order_date DESC;

---

Example 5: Sort Employees by Salary

SELECT *
FROM employees
ORDER BY salary DESC;

---

Example 6: Sort by Department and Salary

SELECT *
FROM employees
ORDER BY department ASC, salary DESC;

---

Example 7: Top 10 Products

SELECT *
FROM products
ORDER BY price DESC
LIMIT 10;

---

Example 8: Customers from a Specific City

SELECT *
FROM customers
WHERE city = 'Jaipur'
ORDER BY name ASC;

---

Example 9: Highest-Spending Customers

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

---

Example 10: Latest Completed Orders

SELECT *
FROM orders
WHERE status = 'Completed'
ORDER BY order_date DESC;

---

ORDER BY in E-Commerce Applications

ORDER BY is essential for product listing and shopping applications.

For example, a user may choose:

Price: Low to High

SELECT *
FROM products
ORDER BY price ASC;

Price: High to Low

SELECT *
FROM products
ORDER BY price DESC;

Newest Products

SELECT *
FROM products
ORDER BY created_at DESC;

Highest-Rated Products

SELECT *
FROM products
ORDER BY rating DESC;

Most Popular Products

SELECT *
FROM products
ORDER BY total_sales DESC;

These queries can form the basis of sorting options in a product listing screen.

---

ORDER BY in Admin Dashboards

Admin dashboards frequently need sorted data.

For example, to display recent orders:

SELECT
    order_id,
    customer_id,
    amount,
    status,
    created_at
FROM orders
ORDER BY created_at DESC;

To display the largest orders:

SELECT
    order_id,
    customer_id,
    amount
FROM orders
ORDER BY amount DESC;

To display pending orders first:

SELECT
    order_id,
    status,
    created_at
FROM orders
ORDER BY CASE status
    WHEN 'Pending' THEN 1
    WHEN 'Processing' THEN 2
    WHEN 'Completed' THEN 3
    ELSE 4
END,
created_at DESC;

---

ORDER BY Performance

Sorting can become expensive when working with large datasets.

The database may need to process many rows before producing the ordered result.

1. Index Appropriate Columns

If an application frequently runs:

SELECT *
FROM products
ORDER BY product_id;

an index on product_id may help the database retrieve rows efficiently.

Whether an index eliminates or reduces sorting work depends on the database, query, index definition, and execution plan.

---

2. Use WHERE Before ORDER BY When Appropriate

For example:

SELECT *
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC;

The database can potentially work with a much smaller subset of rows than if it had to sort the entire orders table.

A composite index such as:

(customer_id, order_date)

may be useful for this type of query, depending on the workload and database.

---

3. Avoid Unnecessary Sorting

If you don’t need a particular order, don’t add unnecessary ORDER BY clauses.

Sorting can require additional database resources.

---

4. Use EXPLAIN

When an ORDER BY query is slow, inspect the execution plan.

For databases that support it:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC;

The execution plan can help identify:

• Index usage
• Sorting operations
• Table scans
• Join strategies
• Estimated costs

---

Common ORDER BY Mistakes

Mistake 1: Assuming Default Row Order

Do not assume:

SELECT *
FROM users;

will always return records in ID or insertion order.

If order matters:

SELECT *
FROM users
ORDER BY id ASC;

---

Mistake 2: Sorting in the Wrong Direction

If you want the highest salary first:

ORDER BY salary DESC;

not:

ORDER BY salary ASC;

---

Mistake 3: Forgetting a Tie-Breaker

Suppose many products have the same price:

ORDER BY price DESC;

The relative order of products with equal prices may not be deterministic.

For predictable pagination, use a unique secondary key:

ORDER BY price DESC, product_id ASC;

This is especially important when implementing pagination.

---

Mistake 4: Using ORDER BY Position Unnecessarily

Although:

ORDER BY 2 DESC;

may work, this is less clear than:

ORDER BY salary DESC;

Prefer column names or meaningful expressions.

---

Mistake 5: Sorting Before Limiting

When finding the highest values, remember that you generally want:

SELECT *
FROM products
ORDER BY price DESC
LIMIT 10;

not a query that limits arbitrary rows before sorting.

---

SQL ORDER BY Cheat Sheet

Requirement	SQL
Ascending	ORDER BY price ASC
Descending	ORDER BY price DESC
Default ascending	ORDER BY price
Multiple columns	ORDER BY city ASC, name ASC
Different directions	ORDER BY department ASC, salary DESC
Sort dates newest first	ORDER BY created_at DESC
Sort dates oldest first	ORDER BY created_at ASC
Sort aggregate result	ORDER BY total_sales DESC
Top results	ORDER BY price DESC LIMIT 10
Sort calculated value	ORDER BY price * quantity DESC
Custom order	ORDER BY CASE ... END
Tie-breaker	ORDER BY price DESC, id ASC

---

Frequently Asked Questions

What is ORDER BY in SQL?

ORDER BY is used to sort query results based on one or more columns or expressions.

---

What is the default ORDER BY direction?

The default is generally ascending (ASC).

ORDER BY price;

is equivalent to:

ORDER BY price ASC;

---

What is the difference between ASC and DESC?

ASC sorts from lower to higher or A to Z.

DESC sorts from higher to lower or Z to A.

---

Can ORDER BY use multiple columns?

Yes.

ORDER BY department ASC, salary DESC;

SQL sorts by the first expression and uses subsequent expressions to break ties.

---

Can ORDER BY be used with WHERE?

Yes.

SELECT *
FROM products
WHERE stock > 0
ORDER BY price ASC;

---

Can ORDER BY be used with GROUP BY?

Yes.

SELECT category, COUNT(*) AS total
FROM products
GROUP BY category
ORDER BY total DESC;

---

Can ORDER BY be used with JOIN?

Yes.

SELECT c.name, o.amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY o.amount DESC;

---

Does SQL guarantee row order without ORDER BY?

No. If a specific order is required, use ORDER BY.

---

How can I get the highest value?

You can use an aggregate:

SELECT MAX(price)
FROM products;

Or sort and limit:

SELECT *
FROM products
ORDER BY price DESC
LIMIT 1;

The second approach can return the complete top row, but if multiple rows tie for the highest value, add appropriate logic if you need all ties.

---

Conclusion

The SQL ORDER BY clause is essential whenever you need to sort database results in a predictable and meaningful way.

It can be used with:

• Numbers
• Text
• Dates
• Multiple columns
• Aggregate functions
• WHERE
• GROUP BY
• HAVING
• JOIN
• LIMIT
• Expressions
• CASE
• Pagination

The most important concepts to remember are:

• ORDER BY sorts query results.
• ASC means ascending order.
• DESC means descending order.
• Ascending is generally the default.
• You can sort using multiple columns.
• Later columns act as tie-breakers.
• WHERE filters rows, while ORDER BY sorts them.
• GROUP BY creates groups, while ORDER BY sorts the resulting groups.
• Don’t rely on row order unless you explicitly use ORDER BY.
• For pagination, use a deterministic ordering, ideally including a unique tie-breaker.
• For large datasets, indexes and execution plans can be important for sorting performance.
• NULL sorting behavior can differ between database systems.

A useful SQL learning sequence is:

SELECT → WHERE → JOIN → GROUP BY → HAVING → ORDER BY → LIMIT → Subqueries → Window Functions

Once you understand ORDER BY, you can build more useful search results, product listings, admin dashboards, reports, rankings, leaderboards, and data-analysis queries.