In this tutorial, the SQL ORDER BY statement is explained with definition, syntax, example, ASC & DESC keyword. Using it, you will learn how to sort multiple column data in ascending or descending order.
ORDER BY clause is mainly used with the SELECT statement. If you don’t know about the SELECT statement, Don’t worry, I will give you basic information about it.
If you need to display data from the table in descending or ascending order, this tutorial is beneficial for you. So, you must follow all the given steps that will learn you a new concept.
What is SQL ORDER BY Clause
Definition – ORDER By clause returns one or more column data in ascending or descending order.
- Ascending Order begins with the lowest value and ends with the largest value.
- Descending Order begins with the largest value and ends with the lowest value.
Read Also –
SQL Interview Questions and Answers
You must memorize the following points –
- ORDER BY can sort the column values in ascending order using the ASC keyword.
- By Default ORDER BY returns column values in ascending order. So, you may not use the ASC keyword.
- ORDER BY can also short the column values in descending order using the DESC keyword
- ORDER By clause always used with SELECT statement.
- You should always use ORDER BY after WHERE. HAVING & GROUP BY clause if you need to use them.
Don’t worry, all the above points will be explained in form the next examples.
ORDER BY Syntax
You can use one of the following syntaxes according to your needs. These syntaxes are created with two columns of a table. once, you learn, You definitely create more columns.
- ASC – Ascending
- DESC – Descending
Syntax – 1
Use this syntax to show data in ascending order.
SELECT column_name1, column_name2, ... FROM table_name WHERE Condition GROUP BY column_name1, column_name2... Having Condition ORDER BY column_name1 ASC, column_name_2 ASC, ... ASC;
You can also use the above syntax without the ASC keyword.
SELECT column_name1, column_name2, ... FROM table_name WHERE Condition GROUP BY column_name1, column_name2... Having Condition ORDER BY column_name1, column_name_2 , ...;
Syntax – 2
Use this syntax to show data in descending order
SELECT column_name1, column_name2, ... FROM table_name WHERE Condition GROUP BY column_name1, column_name2... Having Condition ORDER BY column_name1 DESC, column_name_2 DESC, ... DESC;
You must have to use the DESC keyword in the above syntax
Syntax – 3
Use this syntax to show data in both ascending & descending order
SELECT column_name1, column_name2, ... FROM table_name WHERE Condition GROUP BY column_name1, column_name2... Having Condition ORDER BY column_name1 ASC, column_name_2 DESC, ... ASC;
ORDER BY Example
For ORDER BY Example, A table employees is created with some values. This table has five columns like empolyee_id, full_name, gender, city & salary. To create it use the following queries.
Use the following query to create a table
CREATE TABLE employees
(
employee_id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
full_name varchar(50),
gender varchar(20),
city varchar(100),
salary int(10)
);Use the following query to insert some values in employees
INSERT INTO employees( full_name, gender, city, salary) VALUES
( 'Noor Khan', 'Male', 'Patna', 30000),
('Sunil Kumar', 'Male', 'New Delhi', 45000),
( 'Monika Singh', 'Female', 'Chennai', 30000),
('Aaliya Khan', 'Female', 'Chennai', 40000),
( 'Gaurav Kumar', 'Male', 'Patna', 25000),
('Krishna Kumar', 'Female', 'New Delhi', 60000),
('Rapson Jani', 'Male', 'Patna', 70000),
('Zubair Ahmad', 'Male', 'New Delhi', 25000),
('Sunil Mishra', 'Male', 'Patna', 65000),
('Monika Gupta', 'Female', 'Patna', 32000);
Table Name – employees
+---------------+-------------------+---------------+--------------+---------------+ | employee_id | full_name | gender | city | salary | +---------------+-------------------+---------------+--------------+---------------+ | 201 | Noor Khan | Male | Patna | 30,000 | | 202 | Sunil Kumar | Male | New Delhi | 45,000 | | 203 | Monika Singh | Female | Chennai | 30,000 | | 204 | Aaliya Khan | Female | Chennai | 40,000 | | 205 | Gaurav Kumar | Male | Patna | 25,000 | | 206 | Krishna Kumar | Female | New Delhi | 60,000 | | 207 | Rapson Jani | Male | Patna | 70,000 | | 208 | Zubair Ahmad | Male | New Delhi | 25,000 | | 209 | Sunil Mishra | Male | Patna | 65,000 | | 210 | Monika Gupta | Female | Patna | 32,000 | +---------------+-------------------+---------------+--------------+---------------+
Query – 1
You can show employees name in ascending order using the following query
SELECT full_name FROM employees ORDER BY full_name ASC;
Output – 1
After executing the above query, you will get the employee name in ascending order.
+-------------------+ | full_name | +-------------------+ | Aaliya Khan | | Gaurav Kumar | | Krishna Kumar | | Monika Gupta | | Monika Singh | | Noor Khan | | Rapson Jani | | Sunil Kumar | | Sunil Mishra | | Zubair Ahmad | +-------------------+
Query – 2
You can show employees’ names in descending order using the following query
SELECT full_name FROM employees ORDER BY full_name DESC;
Output – 2
After executing the above query, you will get the employees’ names in descending order.
+-------------------+ | full_name | +-------------------+ | Zubair Ahmad | | Sunil Mishra | | Sunil Kumar | | Rapson Jani | | Noor Khan | | Monika Singh | | Monika Gupta | | Krishna Kumar | | Gaurav Kumar | | Aaliya Kahan | +-------------------+
SQL ORDER BY Ascending ( ASC )
- Ascending Order always begins with the lowest value and ends with the largest value
- SQL Represent Descending by DESC keyword.
- DESC keyword must come with the ORDER BY clause to return data in descending order.
- You can’t fetch column data from a table in descending order without using the DESC keyword with the ORDER BY clause.
Syntax –
SELECT column_name1, column_name2, ... FROM table_name ORDER BY column_name1 ASC, column_name_2 ASC, ... ASC;
Example –
Suppose that we need to display employees’ full name & salary by fetching from the employees’ table. But the salary must be in descending order. So, We can implement it using the following query
Query –
SELECT full_name, salary FROM employees ORDER BY salary DESC;
Output –
+-------------------+---------------+ | full_name | salary | +-------------------+---------------+ | Gaurav Kumar | 25,000 | | Zubair Ahmad | 25,000 | | Noor Khan | 30,000 | | Monika Singh | 30,000 | | Monika Gupta | 32,000 | | Aaliya Khan | 40,000 | | Sunik Kumar | 45,000 | | Krishna Kumar | 60,000 | | Sunil Mishra | 65,000 | | Rapson Jani | 70,000 | +-------------------+---------------+
SQL ORDER BY Descending ( DESC )
- Descending Order always begins with the largest value and ends with the lowest value
- SQL Represent Ascending by ASC keyword.
- ASC keyword may come with the ORDER BY clause to return data in ascending order.
- You can also use the ORDER BY clause without using the ASC keyword to fetch column data from a table in ascending order.
Syntax –
SELECT column_name1, column_name2, ... FROM table_name ORDER BY column_name1 DESC, column_name_2 DESC, ... DESC;
Example –
Suppose that we need to display employees’ full name & salary by fetching from the employees’ table. But the salary must be in ascending order. So, We can implement it using the following query
Query –
SELECT full_name, salary FROM employees ORDER BY salary ASC;
Output –
+-------------------+---------------+ | full_name | salary | +-------------------+---------------+ | Rapson Jani | 70,000 | | Sunil Mishra | 65,000 | | Krishna Kumar | 60,000 | | Sunil Kumar | 45,000 | | Aaliya Khan | 40,000 | | Monika Gupta | 32,000 | | Noor Khan | 30,000 | | Monika Singh | 30,000 | | Gaurav Kumar | 25,000 | | Zubair Ahmad | 25,000 | +-------------------+---------------+
SQL ORDER BY Multiple Columns
You can also get the values of multiple columns in ascending or descending order using ORDER BY statement. Here. Here you will learn its example with two columns. Once you learn it, you will be able to implement ORDER By with more columns.
ORDER BY Two Columns
First, Let’s understand through the following syntaxes & examples that how does ORDER BY work with two column
Syntax – 1
First of all, This syntax returns result order by column_name1 in ascending order, But if column_name1 has the same values in some rows then it returns order by column_name2 in descending order.
SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 ASC, column_name_2 DESC;
Syntax – 2
First of all, This syntax returns result order by column_name2 in descending order, But if column_name2 has the same values in some rows then it returns order by column_name1 in ascending order.
SELECT column_name1, column_name2 FROM table_name ORDER BY column_name_2 DESC, column_name1 ASC;
Example –
Suppose that we need to show employees’ full name & salary from the employees’ table. But full name must be in ascending order and salary must be in descending order.
Query – 1
In this query, the full name is placed in the first position and salary in second place after the ORDER BY clause. So, it returns the output according to the following points.
First of all, This query returns result order by full name in ascending order, But if the same full name in some rows then it returns order by salary in descending order.
SELECT full_name, salary FROM employees ORDER BY full_name ASC, salary DESC;
Output –
+-------------------+---------------+ | full_name | salary | +-------------------+---------------+ | Aaliya Khan | 40,000 | | Gaurav Kumar | 65,000 | | Krishna Kumar | 60,000 | | Monika Gupta | 32,000 | | Monika Singh | 30,000 | | Noor Khan | 30,000 | | Rapson Jani | 70,000 | | Sunil Kumar | 45,000 | | Sunil Mishra | 65,000 | | Zubair Ahmad | 25,000 | +-------------------+---------------+
Query – 2
In this query, the salary is placed in the first position and full name in second place after the ORDER BY clause. So, it returns the output according to the following points.
First of all, This query returns result order by salary in descending order, But if the same salary in some rows then it returns order by full name in ascending order.
SELECT full_name, salary FROM employees ORDER BY salary DESC, full_name ASC;
Output –
+-------------------+---------------+ | full_name | salary | +-------------------+---------------+ | Rapson Jani | 70,000 | | Sunil Mishra | 65,000 | | Krishna Kumar | 60,000 | | Sunil Kumar | 45,000 | | Aaliya Khan | 40,000 | | Monika Gupta | 32,000 | | Monika Singh | 30,000 | | Noor Khan | 30,000 | | Gaurav Kumar | 25,000 | | Zubair Ahmad | 25,000 | +-------------------+---------------+
SQL ORDER BY With Different Clauses
Sometimes, You may need to use SQL ORDER BY clause with different clauses like WHERE, GROUP BY & Having. So, You should also learn it through the following steps –
ORDER BY with WHERE Clause
- The WHERE clause is used with the SELECT statement for a specified condition.
- Even WHERE clause may be used with the ORDER BY clause.
- You must use the WHERE Clause before the ORDER BY clause
If you need to display values in ascending or descending order based on a condition then you can use the WHERE clause with the ORDER BY statement
Syntax – 1
This syntax is created for ascending order
SELECT column_name1, column_name2... FROM table_name WHERE Condition ORDER BY column_name_2 ASC, column_name1 ASC,..;
Syntax – 2
This syntax is created for descending order
SELECT column_name1, column_name2... FROM table_name WHERE Condition ORDER BY column_name_2 DESC, column_name1 DESC,..;
Example –
This example will show the full name & salary of only Male employees. But the full name will be in descending order.
Query –
SELECT full_name, salary FROM employees WHERE gender='Male' ORDER BY full_name DESC;
Output –
+-------------------+---------------+ | full_name | salary | +-------------------+---------------+ | Zubair Ahmad | 25,000 | | Sunil Mishra | 65,000 | | Sunil Kumar | 45,000 | | Rapson Jani | 70,000 | | Noor Khan | 30,000 | | Gaurav Kumar | 25,000 | +-------------------+---------------+
ORDER BY with GROUP BY Clause
- The GROUP BY clause groups the same values and returns the result based on aggregate functions like COUNT(), MIN(), MAX(), SUM() & AVG().
- The GROUP BY clause May be used with the SELECT statement & ORDER BY clause
- You must use the GROUP BY clause after WHERE & before ORDER BY.
Syntax – 1
This syntax is created for ascending order
SELECT column_name1, column_name2... FROM table_name GROUP BY column_name1, column_name2.. ORDER BY column_name_2 ASC, column_name1 ASC,..;
Syntax – 2
This syntax is created for descending order
SELECT column_name1, column_name2... FROM table_name GROUP BY column_name1, column_name2.. ORDER BY column_name_2 DESC, column_name1 DESC,..;
Example –
This example will show the total number of employees and the city name. The Total number of employees will be in descending order and the same city names will be grouped.
Query –
SELECT city, COUNT(full_name) FROM employees GROUP BY city ORDER BY COUNT(full_name) DESC;
Output –
+-------------------+-------------------------+ | full_name | COUNT(full_name) | +-------------------+-------------------------+ | Patna | 5 | | New Delhi | 3 | | Chennai | 2 | +-------------------+-------------------------+
ORDER BY with Having Clause
- The HAVING clause is used to returns result on specified condition using aggregate functions like COUNT(), MIN(), MAX(), SUM() & AVG().
- The HAVING clause May be used with the SELECT statement & ORDER BY clause
- You must use the HAVING clause after WHERE & GROUP and before ORDER BY.
Syntax – 1
This syntax is created for ascending order
SELECT column_name1, column_name2... FROM table_name HAVING Condition ORDER BY column_name_2 ASC, column_name1 ASC,..;
Syntax – 2
This syntax is created for descending order
SELECT column_name1, column_name2... FROM table_name HAVING Condition ORDER BY column_name_2 DESC, column_name1 DESC,..;
Example –
This example will show the total number of employees and the city name. The Total number of employees will less than 5 and the same city names will be grouped and in descending order.
Query –
SELECT city, COUNT(full_name) FROM employees GROUP BY city Having COUNT(full_name) < 5 ORDER BY city DESC;
Output –
+-------------------+-------------------------+ | full_name | COUNT(full_name) | +-------------------+-------------------------+ | New Delhi | 3 | | Chennai | 2 | +-------------------+-------------------------+
ORDER BY with WHERE, GROUP BY & Having Clause
In some cases, you may need to use ORDER BY clause with WHERE, GROUP, & HAVING
Example –
This example will show the total number of only male employees and the city name. The Total number of employees will less than 5 and the same city names will be grouped and in descending order.
Query –
SELECT city, COUNT(full_name) FROM employees WHERE gender='Male' GROUP BY city Having COUNT(full_name) < 5 ORDER BY city DESC;
Output –
+-------------------+-----------------------+ | full_name | COUNT(full_name) | +-------------------+-----------------------+ | Patna | 4 | | New Delhi | 2 | +-------------------+-----------------------+
How to Execute SQL ORDER BY Statement
To execute SQL GROUP BY, you have to configure the following points –
- First of all, make sure that the SQL server is installed on your computer
- Create a MySQL Database like
my_db - Now, Create a table like
employees - Insert some records in the created table
- Write a SELECT query command with an ORDER clause
mysql>CREATE DATABASE my_db;
mysql>USE my_db;
mysql>CREATE TABLE employees (
full_name varchar(50),
gender varchar(20),
city varchar(100),
salary int(10)
);
mysql>INSERT INTO employees( full_name, gender, city, salary) VALUES
( 'Noor Khan', 'Male', 'Patna', 30000),
('Sunil Kumar', 'Male', 'New Delhi', 45000),
( 'Monika Singh', 'Female', 'Chennai', 30000),
('Aaliya Khan', 'Female', 'Chennai', 40000),
( 'Gaurav Kumar', 'Male', 'Patna', 25000),
('Krishna Kumar', 'Female', 'New Delhi', 60000),
('Rapson Jani', 'Male', 'Patna', 70000),
('Zubair Ahmad', 'Male', 'New Delhi', 25000),
('Sunil Mishra', 'Male', 'Patna', 65000),
('Monika Gupta', 'Female', 'Patna', 32000);
mysql>SELECT city, COUNT(full_name) FROM employees
WHERE gender='Male'
GROUP BY city
Having COUNT(full_name) < 5
ORDER BY city DESC;
Tutorial Summary
Dear Developer, I have posted this tutorial according to my working experience. I hope that You have learned how to use SQL ORDER BY. Now you can group duplicate values of any tables in SQL.
If you have any questions related to SQL topics, You can ask me through the comment box. Even you can suggest sharing other topics related to web development.