SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts records in ascending order (A-Z, smallest to largest), but you can specify descending order as well.

The ORDER BY clause is often used in conjunction with SELECT statements to organize the output based on specific criteria.

In this tutorial, we will guide you through the syntax of SQL ORDER BY clause, and how to use it in SQL statements with the help of examples.

Syntax of SQL ORDER BY Clause

The basic syntax of the SQL ORDER BY clause is as follows:

</>
Copy
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns to be retrieved from the table.
  • FROM: Specifies the table from which to retrieve data.
  • ORDER BY: Specifies the columns used to sort the result set.
  • ASC: Optional. Sorts the column in ascending order (default behavior).
  • DESC: Optional. Sorts the column in descending order.

You can sort by multiple columns, specifying ASC or DESC for each column individually.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the ORDER BY clause in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a table named students:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

Examples: Using ORDER BY Clause in Queries

Now, let’s explore different scenarios of using the ORDER BY clause with this table.

SQL ORDER BY

1. Sorting Results in Ascending Order

To select all students and sort them by age in ascending order:

</>
Copy
SELECT * FROM students
ORDER BY age ASC;

This query sorts the results by age from youngest to oldest. Since ascending order is the default, the ASC keyword is optional.

SQL ORDER BY - Sorting Results in Ascending Order

2. Sorting Results in Descending Order

To select all students and sort them by age in descending order:

</>
Copy
SELECT * FROM students
ORDER BY age DESC;

This query sorts the results by age from oldest to youngest by specifying DESC after the ORDER BY clause.

SQL ORDER BY - Sorting Results in Descending Order

3. Sorting by Multiple Columns

To sort students by grade in ascending order and then by age in descending order within each grade:

</>
Copy
SELECT * FROM students
ORDER BY grade ASC, age DESC;

This query sorts by grade first (ascending), and for students with the same grade, it sorts by age (descending).

SQL ORDER BY - Sorting by Multiple Columns

4. Sorting Results Alphabetically

To sort students by name in alphabetical order:

</>
Copy
SELECT * FROM students
ORDER BY name ASC;

This query sorts students alphabetically by their name. Alphabetical order is the default for strings, so ASC is optional.

SQL ORDER BY - Sorting Results Alphabetically

FAQs for SQL ORDER BY

1. What does the SQL ORDER BY clause do?

The ORDER BY clause sorts the result set of a query by one or more specified columns, either in ascending or descending order.

2. Can I sort by multiple columns with ORDER BY?

Yes, you can sort by multiple columns. Just list each column in the ORDER BY clause, separated by commas, and specify ASC or DESC for each column if needed.

3. What is the default sorting order for ORDER BY?

The default sorting order for ORDER BY is ascending (ASC), meaning numbers from smallest to largest and strings alphabetically from A to Z.

4. Can I use ORDER BY with NULL values?

Yes, ORDER BY handles NULL values by placing them at the end when sorting in ascending order or at the beginning for descending order.

5. How does ORDER BY work with string columns?

When sorting string columns, ORDER BY arranges text alphabetically (A-Z) for ascending order and reverse-alphabetically (Z-A) for descending order.