SQL - Sorting Results

SQL - Sorting Query Results

When you query a database, the rows are not guaranteed to be in any specific order unless you explicitly ask for one. The ORDER BY clause is the standard SQL way to sort the result set of a query by one or more columns. Proper sorting is crucial for creating user-friendly applications, from displaying products by price to listing comments by date.

Syntax

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

Let’s work with a CUSTOMERS table for our examples:

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    CITY VARCHAR(25),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(ID, NAME, AGE, CITY, SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', NULL); -- Note the NULL salary

Basic Sorting: ASC and DESC

You can sort data in two directions:

• ASC (Ascending): Sorts from lowest to highest (A-Z, 0-9). This is the default behavior if you don’t specify a direction.
• DESC (Descending): Sorts from highest to lowest (Z-A, 9-0).

Example: Ascending Order (Default)

This query sorts customers alphabetically by name.

SELECT NAME, CITY, AGE FROM CUSTOMERS ORDER BY NAME ASC;
-- This is equivalent to: SELECT NAME, CITY, AGE FROM CUSTOMERS ORDER BY NAME;

Example: Descending Order

This query sorts customers by age, from oldest to youngest.

SELECT NAME, AGE FROM CUSTOMERS ORDER BY AGE DESC;

Handling NULL Values in Sorting

A common question is: where do NULL values go when sorting? The SQL standard does not strictly define a default, so different databases behave differently. PostgreSQL, for example, treats NULLs as larger than any other value, while Oracle treats them as smaller.

To explicitly control this, you can use the NULLS FIRST or NULLS LAST syntax in most modern databases.

-- Sort by salary, but ensure customers with unknown salaries appear at the very end.
SELECT NAME, SALARY FROM CUSTOMERS ORDER BY SALARY ASC NULLS LAST;

Sorting by Multiple Columns

You can provide a comma-separated list of columns to the ORDER BY clause. The result set is sorted by the first column, and then for rows with the same value in the first column, they are sorted by the second column, and so on.

Example: Sorting by Age, then Name

Let’s sort our customers by age in descending order. If two customers have the same age, sort them alphabetically by name.

SELECT NAME, AGE FROM CUSTOMERS ORDER BY AGE DESC, NAME ASC;

In the output, ‘Chaitali’ (age 25) would appear before ‘Khilan’ (age 25) because ‘C’ comes before ‘K’.

Advanced Sorting Techniques

Sorting by a Custom Logic with CASE

Sometimes you need to sort by a non-standard, custom order. The CASE statement is perfect for this. For example, you want to feature customers from ‘Mumbai’ and ‘Delhi’ first, then list the rest.

SELECT NAME, CITY FROM CUSTOMERS
ORDER BY
    CASE
        WHEN CITY = 'Mumbai' THEN 1
        WHEN CITY = 'Delhi'  THEN 2
        ELSE 3
    END ASC,
    NAME ASC;

Sorting by a Calculated Expression

You can also sort by the result of an expression or function. For example, if you wanted to sort by the length of a customer’s name:

-- Note: LENGTH() function name may vary (e.g., LEN() in SQL Server).
SELECT NAME, LENGTH(NAME) AS NameLength FROM CUSTOMERS ORDER BY NameLength DESC;

Performance Considerations: The Role of Indexes

Sorting a large result set can be a slow and memory-intensive operation for the database. If you frequently sort by a specific column or set of columns, creating a database index on them can dramatically improve performance. An index is a special data structure that allows the database to find and order rows much faster, often avoiding a full data sort entirely.

For example, if your application has a page that always shows customers sorted by SALARY DESC, creating an index on the SALARY column is a critical optimization.

CREATE INDEX idx_customers_salary ON CUSTOMERS (SALARY);