• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

SQL SORTING Results

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default.

Syntax:

The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.

Example:

Consider CUSTOMERS table is having following records:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS    | SALARY   |

+—-+———-+—–+———–+———-+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+—-+———-+—–+———–+———-+

Following is an example which would sort the result in ascending order by NAME and SALARY:

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;

This would produce following result:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  1 | Ramesh   |  32 | Ahmedabad |   2000.00 |

+—-+———-+—–+———–+———-+

Following is an example which would sort the result in descending order by NAME:

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

This would produce following result:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  1 | Ramesh   |  32 | Ahmedabad |   2000.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

+—-+———-+—–+———–+———-+

To fetch the rows with own preferred order, the SELECT query would as follows:

SQL> SELECT * FROM CUSTOMERS ORDER BY (CASE ADDRESS

WHEN ‘DELHI’           THEN 1

WHEN ‘BHOPAL’          THEN 2

WHEN ‘KOTA’   THEN 3

WHEN ‘AHMADABAD’ THEN 4

WHEN ‘MP’    THEN 5

ELSE 100 END) ASC, ADDRESS DESC;

This would produce following result:

+—-+———-+—–+———–+———-+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+—-+———-+—–+———–+———-+

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  1 | Ramesh   |  32 | Ahmedabad |   2000.00 |

+—-+———-+—–+———–+———-+

This will sort customers by ADDRESS in your ownoOrder of preference first and in a natural  order for the remaining addresses. Also remaining Addresses will be sorted in the reverse alpha order.

 

Summary
Review Date
Reviewed Item
SQL SORTING Results
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on SQL SORTING Results"

    Leave a Message

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

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.