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.