SQL | ORDER BY

The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.

  • By default ORDER BY sorts the data in ascending order.
  • We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Syntax of all ways of using ORDER BY is shown below:

  • Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.
    Syntax:



    SELECT * FROM table_name ORDER BY column_name ASC|DESC
    
    table_name: name of the table.
    column_name: name of the column according to which the data is needed to be arranged.
    ASC: to sort the data in ascending order.
    DESC: to sort the data in descending order.
    | : use either ASC or DESC to sort in ascending or descending order
    
  • Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.
    Syntax:

    SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC
    

 

Screenshot from 2016-12-19 12-53-29

Queries:

  • Sort according to single column: In this example we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO.
    SELECT * FROM Student ORDER BY ROLL_NO DESC;
    

    Output:

    ROLL_NO NAME ADDRESS PHONE Age
    8 NIRAJ ALIPUR XXXXXXXXXX 19
    7 ROHIT BALURGHAT XXXXXXXXXX 18
    6 DHANRAJ BARABAJAR XXXXXXXXXX 20
    5 SAPTARHI KOLKATA XXXXXXXXXX 19
    4 DEEP RAMNAGAR XXXXXXXXXX 18
    3 RIYANKA SILIGURI XXXXXXXXXX 20
    2 PRATIK BIHAR XXXXXXXXXX 19
    1 HARSH DELHI XXXXXXXXXX 18

    To sort in ascending order we have to use ASC in place of DESC.

  • Sort according to multiple columns:In this example we will fetch all data from the table Student and then sort the result in ascending order first according to the column Age. and then in descending order according to the column ROLL_NO.
    SELECT * FROM Student ORDER BY Age ASC , ROLL_NO DESC;
    

    Output:

    ROLL_NO NAME ADDRESS PHONE Age
    7 ROHIT BALURGHAT XXXXXXXXXX 18
    4 DEEP RAMNAGAR XXXXXXXXXX 18
    1 HARSH DELHI XXXXXXXXXX 18
    8 NIRAJ ALIPUR XXXXXXXXXX 19
    5 SAPTARHI KOLKATA XXXXXXXXXX 19
    2 PRATIK BIHAR XXXXXXXXXX 19
    6 DHANRAJ BARABAJAR XXXXXXXXXX 20
    3 RIYANKA SILIGURI XXXXXXXXXX 20

    In the above output you can see that first the result is sorted in ascending order according to Age.

    There are multiple rows having same Age. Now, sorting further this result-set according to ROLL_NO will sort the rows with same Age according to ROLL_NO in descending order.

  • Note that: ASC is the default value for ORDER BY clause. So, if you don’t specify anything after column name in ORDER BY clause, the output will be sorted in ascending order by default.

    Example: The following query will give similar output as the above:

    SELECT * FROM Student ORDER BY Age , ROLL_NO DESC;
    

    Output:

    ROLL_NO NAME ADDRESS PHONE Age
    7 ROHIT BALURGHAT XXXXXXXXXX 18
    4 DEEP RAMNAGAR XXXXXXXXXX 18
    1 HARSH DELHI XXXXXXXXXX 18
    8 NIRAJ ALIPUR XXXXXXXXXX 19
    5 SAPTARHI KOLKATA XXXXXXXXXX 19
    2 PRATIK BIHAR XXXXXXXXXX 19
    6 DHANRAJ BARABAJAR XXXXXXXXXX 20
    3 RIYANKA SILIGURI XXXXXXXXXX 20

This article is contributed by Harsh Agarwal If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up

Improved By : aditimantri2196



Article Tags :
Practice Tags :


6


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.