• Ekta Aggarwal

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;

Tags: