This tutorial will explain how to sort data from a table in SQL and how to use them with the examples.
This is the general statement to sort rows from a table
SELECT
column1,
column2,
...
FROM
tablename
ORDER BY
column1 | expression [ASC | DESC ],
column2 | expression [ASC | DESC ],
...
Lets create a sample table and add some rows
CREATE TABLE SampleContacts (
FullName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Age INT NOT NULL
);
INSERT INTO SampleContacts
(FullName, Gender, Age)
VALUES
('Contact1', 'M', 26),
('Contact2', 'M', 29),
('Contact3', 'F', 27),
('Contact4', 'F', 28),
('Contact05', 'F', 25)
('Contact6', 'M', 24)
1) Sort a table by single column in ascending order
The following statement retrieves all records from SampleContacts table than sort by the Age in ascending order. We did not use ASC or DESC on this example, as default ASC is used.
SELECT
FullName,
Gender,
Age
FROM
SampleContacts
ORDER BY
Age
| FullName | Gender | Age |
-------------------------------
Contact6 M 24
Contact05 F 25
Contact1 M 26
Contact3 F 27
Contact4 F 28
Contact2 M 29
2) Sort a table by single column in descending order
The following statement retrieves all records from SampleContacts table than sort by the Age in descending order.
SELECT
FullName,
Gender,
Age
FROM
SampleContacts
ORDER BY
Age DESC
| FullName | Gender | Age |
-------------------------------
Contact2 M 29
Contact4 F 28
Contact3 F 27
Contact1 M 26
Contact05 F 25
Contact6 M 24
3) Sort a table by multiple columns
The following statement retrieves all records from SampleContacts table than sort by first Gender after that it would sort by Age within each Gender group.
SELECT
FullName,
Gender,
Age
FROM
SampleContacts
ORDER BY
Gender, Age
As ASC and DESC values have not been specified the default value ASC will be used for Gender and Age ordering process
| FullName | Gender | Age |
-------------------------------
Contact05 F 25
Contact3 F 27
Contact4 F 28
Contact6 M 24
Contact1 M 26
Contact2 M 29
4) Sort a table by multiple columns and different orders
The following statement retrieves all records from SampleContacts table than sort by first Gender in ascending order after that it would sort by Age in descending order within each Gender group.
SELECT
FullName,
Gender,
Age
FROM
SampleContacts
ORDER BY
Gender ASC, Age DESC
| FullName | Gender | Age |
-------------------------------
Contact4 F 28
Contact3 F 27
Contact05 F 25
Contact2 M 29
Contact1 M 26
Contact6 M 24
5) Sort a table by an expression
The LEN() function returns the number of characters in a string. The following statement retrieves all records from SampleContacts table than sort by the length of the FullName:
SELECT
FullName,
Gender,
Age
FROM
SampleContacts
ORDER BY
LEN(FullName)
| FullName | Gender | Age |
-------------------------------
Contact1 M 26
Contact2 M 29
Contact3 F 27
Contact4 F 28
Contact6 M 24
Contact05 F 25