BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

SQL ORDER BY Clause

Last Updated: November 28, 2018 by Chaitanya Singh | Filed Under: SQL

We know that SQL SELECT Statement returns the rows in no particular order. With the help of ORDER BY clause, we can order the rows in a desired order. ORDER BY clause is used to sort the returned records in an order. By using ORDER BY clause, we can sort the result in ascending or descending order. This clause can be used with multiple columns as well.

ORDER BY Syntax

SELECT column_name1, column_name2, column_name3.... 
FROM table_name
WHERE Condition 
ORDER BY column_name1, column_name2, .... (ASC OR DESC);

Here ASC is used for sorting in ascending order and DESC for descending order.

SQL ORDER BY clause example

Lets say we have a table “STUDENT” with the following records.

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

The following SQL statement will fetch all the records from the table “STUDENT” and the returned rows will be in ascending order of student age.

SELECT * 
   FROM STUDENT
ORDER BY AGE;

Note: The default order of sorting is ascending so the rows will be sorted based on the column “AGE”, even though we have not used the ASC keyword in order by clause. So it is safe to say that the following query is same as the above query and would fetch the same result.

SELECT * 
   FROM STUDENT
ORDER BY AGE ASC;

Result:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
+---------+----------+-----+-----------+----------+

SQL ORDER BY DESC example

Table: STUDENT

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

The following SQL statement will fetch the student names from the table “STUDENT” and the returned names will be sorted in descending order (we have used DESC for descending order in the ORDER BY clause).

SELECT NAME 
   FROM STUDENT
ORDER BY NAME DESC;

Result:

+----------+
| NAME     | 
+----------+
| Steve    | 
| Rick     |  
| Richard  |  
| Peter    | 
| Mark     |  
| Kate     | 
| Brad     |  
+----------+

As you can see, we have got a list of student names sorted in the descending order.

SQL ORDER BY Multiple Columns

In the above examples, we have done the sorting based on a single column. However we can use multiple columns in ORDER BY clause.

When multiple columns are used in ORDER BY, first the rows will be sorted based on the first column and then by the second column. Lets take an example to understand this.

Table: EMPLOYEE

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1001     Paul       India    Noida   201310
1005     Raul       India    Agra    283126
1002     Ajeet      India    Noida   201301
1003     Jon        India    Agra    282008
1006     David      India    Agra    282005
1004     Chetan     India    Noida   201313

Sorting the rows based on the city first and then by pin code within each city:

SELECT * 
   FROM EMPLOYEE
ORDER BY CITY, PINCODE;

Result:
As you can see that the rows are sorted by CITY and then within city sorted by pincode.

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1006     David      India    Agra    282005
1003     Jon        India    Agra    282008
1005     Raul       India    Agra    283126
1002     Ajeet      India    Noida   201301
1001     Paul       India    Noida   201310
1004     Chetan     India    Noida   201313

Sorting the rows based on the city first in ascending order and then by pin code in descending order within each city:

SELECT * 
   FROM EMPLOYEE
ORDER BY CITY ASC, PINCODE DESC;

Result:
As you can see that the rows are sorted by CITY in ascending order and then within city sorted in reverse order by pincode.

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1005     Raul       India    Agra    283126
1003     Jon        India    Agra    282008
1006     David      India    Agra    282005
1004     Chetan     India    Noida   201313
1001     Paul       India    Noida   201310
1002     Ajeet      India    Noida   201301

Top Related Articles:

  1. Introduction to SQL
  2. SQL Syntax
  3. SQL SELECT DISTINCT Statement
  4. DELETE Query in SQL
  5. SQL – Combining AND, OR and NOT together in where clause

About the Author

I have 15 years of experience in the IT industry, working with renowned multinational corporations. Additionally, I have dedicated over a decade to teaching, allowing me to refine my skills in delivering information in a simple and easily understandable manner.

– Chaitanya

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2025 BeginnersBook . Privacy Policy . Sitemap