ORDER BY and Sorting Data

Difficulty: Basic
Generated on: 2025-07-10 02:21:51
Category: SQL Cheatsheet for Database Development

---

SQL ORDER BY and Sorting Data Cheatsheet (Basic Level)

1. Quick Overview

The ORDER BY clause in SQL is used to sort the result-set of a SELECT query. It allows you to arrange the retrieved data in ascending (ASC) or descending (DESC) order based on one or more columns. Sorting is crucial for presenting data in a meaningful and easily understandable way to users.

When to use:

• Displaying data in a specific order (e.g., alphabetically, chronologically, by price).
• Generating reports that require sorted data.
• Implementing pagination or top N queries.
• Optimizing query performance in conjunction with indexes.

2. Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition -- Optional WHERE clause
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• ORDER BY: Keyword to specify the sorting criteria.
• column1, column2, ...: The columns used for sorting. You can sort by multiple columns, with the first column having the highest priority.
• ASC: Ascending order (default). Sorts from smallest to largest (e.g., A-Z, 1-10).
• DESC: Descending order. Sorts from largest to smallest (e.g., Z-A, 10-1).

3. Common Use Cases

• Alphabetical Sorting: Sorting a list of customers by name.
• Chronological Sorting: Sorting a list of orders by date.
• Numeric Sorting: Sorting a list of products by price.
• Sorting by Multiple Columns: Sorting a list of employees first by department and then by salary.
• Sorting with WHERE Clause: Sorting products by price within a specific category.

4. Best Practices

• Use Indexes: Creating indexes on the columns used in ORDER BY can significantly improve query performance, especially for large tables. Consider composite indexes when sorting by multiple columns.
• Avoid Sorting Unnecessarily: Only use ORDER BY when the order of the results is important to the application. Unnecessary sorting can add overhead.
• Limit Results with LIMIT: When combined with ORDER BY, LIMIT allows you to retrieve the top N or bottom N records based on the sorting criteria (e.g., top 10 highest-paying employees).
• Consider Collation: Be aware of collation settings, especially when sorting strings. Collation determines how strings are compared (e.g., case-sensitive or case-insensitive).
• Use NULLS FIRST or NULLS LAST (PostgreSQL, Oracle, SQL Server 2022+): Control the placement of NULL values in the sorted results.

5. Examples

Sample Data (Employees Table):

employee_id	first_name	last_name	department	salary	hire_date
1	John	Doe	Sales	60000	2022-01-15
2	Jane	Smith	Marketing	70000	2021-05-20
3	Peter	Jones	Sales	55000	2023-03-10
4	Alice	Brown	HR	80000	2020-11-01
5	Bob	Wilson	Marketing	65000	2022-08-01
6	Charlie	Davis	Sales	60000	2022-06-20
7	David	Miller	HR	75000	2021-02-15
8	Emily	Garcia	IT	90000	2019-09-01
9	Frank	Rodriguez	IT	NULL	2020-04-10
10	Grace	Williams	Sales	NULL	2023-12-01

Example 1: Sort employees by last name in ascending order.

SELECT employee_id, first_name, last_name
FROM Employees
ORDER BY last_name ASC;

Output:

employee_id	first_name	last_name
4	Alice	Brown
6	Charlie	Davis
1	John	Doe
8	Emily	Garcia
3	Peter	Jones
7	David	Miller
9	Frank	Rodriguez
2	Jane	Smith
10	Grace	Williams
5	Bob	Wilson

Example 2: Sort employees by salary in descending order.

SELECT employee_id, first_name, last_name, salary
FROM Employees
ORDER BY salary DESC;

Output:

employee_id	first_name	last_name	salary
8	Emily	Garcia	90000
4	Alice	Brown	80000
7	David	Miller	75000
2	Jane	Smith	70000
5	Bob	Wilson	65000
1	John	Doe	60000
6	Charlie	Davis	60000
3	Peter	Jones	55000
9	Frank	Rodriguez	NULL
10	Grace	Williams	NULL

Example 3: Sort employees by department and then by salary in descending order.

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

Output:

employee_id	first_name	last_name	department	salary
4	Alice	Brown	HR	80000
7	David	Miller	HR	75000
8	Emily	Garcia	IT	90000
9	Frank	Rodriguez	IT	NULL
2	Jane	Smith	Marketing	70000
5	Bob	Wilson	Marketing	65000
1	John	Doe	Sales	60000
6	Charlie	Davis	Sales	60000
3	Peter	Jones	Sales	55000
10	Grace	Williams	Sales	NULL

Example 4: Sort employees hired in 2022 by hire date in ascending order.

SELECT employee_id, first_name, last_name, hire_date
FROM Employees
WHERE YEAR(hire_date) = 2022 -- Using YEAR() function.  Database specific date functions may be necessary
ORDER BY hire_date ASC;

Output:

employee_id	first_name	last_name	hire_date
1	John	Doe	2022-01-15
6	Charlie	Davis	2022-06-20
5	Bob	Wilson	2022-08-01

Example 5: Sort with NULLS handling (PostgreSQL, Oracle, SQL Server 2022+).

-- PostgreSQL
SELECT employee_id, first_name, last_name, salary
FROM Employees
ORDER BY salary DESC NULLS LAST; -- NULL values at the end.

-- Oracle
SELECT employee_id, first_name, last_name, salary
FROM Employees
ORDER BY salary DESC NULLS LAST;

-- SQL Server 2022+
SELECT employee_id, first_name, last_name, salary
FROM Employees
ORDER BY salary DESC NULLS LAST;

This example places employees with NULL salaries at the end of the sorted list. If NULLS FIRST is used, they would be at the beginning. Note that older versions of SQL Server do not support NULLS FIRST or NULLS LAST directly. Workarounds are needed (see Common Pitfalls).

6. Common Pitfalls

• Forgetting ASC or DESC: If you don’t specify ASC or DESC, the default is ASC (ascending).

• Sorting by Non-Existent Columns: SQL will return an error if you try to sort by a column that doesn’t exist in the table or the SELECT list.

• Performance Issues with Large Datasets: Sorting large datasets without proper indexing can be slow. Analyze query execution plans to identify performance bottlenecks.

• Incorrect Data Types: Ensure that the data type of the column you’re sorting is appropriate for the intended order. Sorting strings as numbers or vice-versa can lead to unexpected results.

• Implicit Sorting: Don’t rely on implicit sorting. SQL does not guarantee a specific order if ORDER BY is not specified.

• NULL Handling (SQL Server < 2022): Older versions of SQL Server do not directly support NULLS FIRST or NULLS LAST. You can use a CASE expression or ISNULL function to achieve the desired behavior:

  -- SQL Server < 2022 workaround for NULLS LAST
  SELECT employee_id, first_name, last_name, salary
  FROM Employees
  ORDER BY
      CASE WHEN salary IS NULL THEN 1 ELSE 0 END, -- Sort NULLs last
      salary DESC;

  This workaround sorts rows where salary is NULL after rows where salary is not NULL, effectively placing NULL values at the end when sorting by salary DESC.

• Collation Issues: Be aware of collation settings when sorting strings. Different collations can result in different sorting orders (e.g., case-sensitive vs. case-insensitive). Specify the collation explicitly in the ORDER BY clause if needed. Example:

  -- Example (SQL Server): Case-insensitive sorting
  SELECT employee_id, first_name, last_name
  FROM Employees
  ORDER BY last_name COLLATE Latin1_General_CI_AI;

  This ensures the results are ordered case-insensitively. The specific collation depends on your database and requirements.

7. Database Variations

• MySQL: MySQL generally follows the standard SQL syntax. Indexing is crucial for performance.
• PostgreSQL: PostgreSQL supports NULLS FIRST and NULLS LAST for controlling the placement of NULL values. Also known for its robust indexing capabilities.
• SQL Server: SQL Server 2022 and later versions support NULLS FIRST and NULLS LAST. Older versions require workarounds using CASE expressions or ISNULL. Pay attention to collation settings.
• Oracle: Oracle supports NULLS FIRST and NULLS LAST. Indexing and query optimization are essential for performance. Oracle also allows sorting based on functions applied to columns in the ORDER BY clause.