Sorting data in SQL (ORDER BY)

In SQL, ORDER BY is used to sort data on the basis of single or multiple columns in ascending or descending order.

Dataset:

For this tutorial we shall make use of employee_performance:

CREATE TABLE employee_performance(
employee_id int,
department varchar(20),
education_level varchar(20) ,
gender char(1),
no_of_trainings int,
age int,
previous_year_rating numeric(2,1),
length_of_service int,
KPIs_met char(1),
avg_training_score numeric(5,2),
promoted_or_not varchar(3))

INSERT INTO
employee_performance(employee_id,department,education_level,gender,no_of_trainings,age,previous_year_rating,length_of_service,KPIs_met,avg_training_score,promoted_or_not)
VALUES
(1001,'Marketing','Graduate','M',2,24,NULL,1,'Y',69.5,'N'),
(1002,'Analytics','Post-Graduate','M',5,32,4.5,7,'Y',89.5,'Y'),
(1003,'R&D','Graduate','F',5,44,5,10,'Y',87,'Y'),
(1004,'HR','Graduate','M',1,32,3,3,'Y',54,'N'),
(1005,'Marketing','PhD','M',0,38,4,7,'N',79.5,'N'),
(1006,'IT','Graduate','F',2,23,NULL,0,'Y',83.5,'N'),
(1007,'Analytics','Post-Graduate','M',2,28,5,4,'Y',78.5,'Y'),
(1008,'Sales','Graduate','F',5,22,NULL,0,'Y',69.5,'N'),
(1009,'Marketing','PhD','F',2,48,3,7,'Y',98,'Y'),
(10010,'Marketing','PhD','M',10,39,5,4,'Y',71,'N'),
(10011,'Finance','Post-Graduate','M',3,35,3.5,10,'N',55,'N'),
(10012,'Sales','Post-Graduate','F',2,55,4,19,'Y',87,'Y'),
(10013,'HR','Graduate','M',0,48,2.5,21,'Y',72.5,'N'),
(10014,'IT','Graduate','F',2,24,3.5,12,'Y',64,'N');

Our dataset looks as follows:

SELECT * FROM employee_performance;

Sorting in ascending order

Task: Sort the employee_performance table on the basis of department name.

SELECT * FROM employee_performance
ORDER BY department;

Alternatively:

SELECT * FROM employee_performance
ORDER BY department ASC;

Note: By default, SQL sorts data in ascending order if order by command is being used.

Sorting in descending order

Task: Sort the employee_performance table on the basis of department name in descending order. To sort in descending order firstly write the column name and then write the keyword DESC after it.

SELECT * FROM employee_performance
ORDER BY department DESC;

Sorting by multiple columns

Task: Sort the employee_performance table on the basis of department name and length_of_service in descending order.

SELECT * FROM employee_performance
ORDER BY department, length_of_service DESC;

Since DESC is written in front of length_of_service only thus note that department names are sorted in ascending order by default and length_of_service in descending order.

To arrange both the columns by descending order we need to write DESC in front of both of them:

SELECT * FROM employee_performance
ORDER BY department DESC, length_of_service DESC;