SQL ORDER BY Sorting and Ranking

SQL ORDER BY Sorting and Ranking

The ORDER BY clause sorts the result set by one or more columns or expressions. Without ORDER BY, the database does not guarantee any particular row order.

Basic Syntax

SELECT name, salary, hire_date
FROM employees
ORDER BY salary DESC, hire_date ASC;

ASC (ascending) is the default. DESC sorts in descending order.

Multi-Column Sorting

When sorting by multiple columns, each column defines a sub-sort within groups of identical values from the previous column:

SELECT * FROM products
ORDER BY category ASC, price DESC, name ASC;

Products are grouped by category alphabetically, then within each category sorted by price highest first, then by name for items with identical prices.

Sorting by Expressions

SELECT name, price, discount
FROM products
ORDER BY price * (1 - discount) ASC;

You can sort by calculated values, function results, or CASE expressions.

NULL Ordering

Different databases handle NULL sorting differently. Use explicit control where supported:

-- PostgreSQL / Oracle
SELECT * FROM contacts
ORDER BY phone NULLS LAST;

-- MySQL alternative
SELECT * FROM contacts
ORDER BY phone IS NULL, phone;

CASE-Based Custom Sorting

SELECT * FROM tickets
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low' THEN 4
  END;

Sorting by Column Position

SELECT name, department, salary
FROM employees
ORDER BY 2, 3 DESC;

While positional sorting works, it is fragile and harder to maintain. Use column names for clarity.

Performance Considerations

• ORDER BY on indexed columns is significantly faster
• Sorting large result sets without an index requires a temporary file sort
• LIMIT combined with ORDER BY allows the optimizer to use a top-N sort, which is more efficient than sorting the entire result set
• Avoid ORDER BY in subqueries unless required by LIMIT or OFFSET

ORDER BY is critical for presenting data in a predictable, user-friendly order and is often combined with LIMIT for pagination.