SQL ORDER BY – Definition, Syntax, Example

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.

sql order by

 

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_idfull_namegendercity  &  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.

Categories SQL

Leave a Comment