How to use the SQL ORDER BY statement

The order BY statement is used to sort the result set based on the specified columns.

The order BY statement sorts the records by default in ascending sequence.

If you want to sort records in descending order, you can use the DESC keyword.

Instance

SELECT Company, OrderNumber from Orders by company, OrderNumber

Note: There are two equal company names (www.111cn.net) in the above results. Only this time, when the first column has the same value, the second column is sorted in ascending order. This is true if some of the values in the first column are nulls.

Instance

MySQL Tutorials > CREATE TABLE Employee (
   ->     id             int,
   ->     first_ name    VARCHAR,
   ->     last_name      VARCHAR,
   ->     start_date    Date,
   ->     end_date      date,
    ->     salary        FLOAT (8,2),
    ->     city          VARCHAR (Ten),
   ->     description   VARCHAR (a)
   ->);
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (4, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (5, ' Robert ', ' black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' Tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql> SELECT * FROM employee order by city;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.02 sec)

The order by can also sort the dates,
Instance

Mysql>
Mysql>
mysql> CREATE TABLE Employee (
   ->     id            int,
   ->      first_name    VARCHAR,
   ->     last_name     VARCHAR,
   ->     start_date     DATE,
   ->     end_date       DATE,
   ->     salary        FLOAT (8,2),
   ->     city           VARCHAR,
   ->     description   VARCHAR (15)
   ->);
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (4, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (5, ' Robert ', ' black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' Tester ');
Query OK, 1 row affected (0.01 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' Tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 9 2 David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql> SELECT first_name, start_date from employee order by start_date;
+------------+------------+
| first_name | start_date |
+------------+------------+
| Alison | 1976-03-21 |
| James | 1978-12-12 |
| Celia | 1982-10-24 |
| Robert | 1984-01-15 |
| Linda | 1987-07-30 |
| David | 1990-12-31 |
| Jason | 1996-07-25 |
| James | 1996-09-17 |
+------------+------------+
8 rows in Set (0.00 sec)

This makes it easy and quick to sort by storing data in date form in our database tutorial.