SQL for QA: Tutorial#6 – ORDER BY Clause
In SQL, ORDER BY clause is used to sort the records of the table. It is used with SELECT statement.
Using this clause, we can sort the data by Ascending or Descending order.
By default, ORDER BY clause sort the data in Ascending order, for Descending order we need to write down DESC keyword explicitly.
We can apply for Order By clause on multiple columns also.
Syntax:
|
1 2 3 4 |
SELECT Column_Name(s) FROM <Tablename> WHERE Condition //Optional ORDER BY Column_Name(s) DESC ; // by default Ascending order |
Let’s take a sample table Employee:
|
1 |
SELECT * FROM Employee; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
ID | Name | Department | Salary -- -+----------+--------------+------- 1 | Anita | IT | 10000 2 | Nita | Testing | 30000 3 | Sangita | | 30000 4 | Supriya | Testing | 60000 5 | Shilpa | Testing | 50000 102 | Mahesh | IT | 18000 105 | Priya | HR | 40000 106 | Priya | Testing | 42000 103 | Ganesh | IT | 30800 101 | Jamuna | HR | 85800 104 | Supriya | IT | 66000 |
Example 1: Use of ORDER BY clause.
|
1 |
SELECT * FROM Employee ORDER BY Name; |
Table sorted as per Name column. By default it sorts the data in ascending order of employee name.
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
ID | Name | Department | Salary ----+---------+------------+------- 1 | Anita | IT | 10000 103 | Ganesh | IT | 30800 101 | Jamuna | HR | 85800 102 | Mahesh | IT | 18000 2 | Nita | Testing | 30000 105 | Priya | HR | 40000 106 | Priya | Testing | 42000 3 | Sangita | | 30000 5 | Shilpa | Testing | 50000 4 | Supriya | Testing | 60000 104 | Supriya | IT | 66000 |
Example 2: By using DESC keyword
|
1 |
SELECT * FROM Employee ORDER BY Name DESC; |
OUTPUT is displayed in Descending Sorted order by Name column.
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
ID | Name | Department | Salary ----+---------+------------+------- 4 | Supriya | Testing | 60000 104 | Supriya | IT | 66000 5 | Shilpa | Testing | 50000 3 | Sangita | | 30000 106 | Priya | Testing | 42000 105 | Priya | HR | 40000 2 | Nita | Testing | 30000 102 | Mahesh | IT | 18000 101 | Jamuna | HR | 85800 103 | Ganesh | IT | 30800 1 | Anita | IT | 10000 |
Example 3: Order by clause with Multiple Columns
The given SQL statement, the table get sorted on the basis of 2 columns. First, it sorts and displays all the department and then under the departments its employee get displayed in sorted order.
First, it will get sorted by Department and then under the Department by Name
SELECT Department, Name FROM Employee ORDER BY Department, Name;
OUTPUT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Department | Name ------------+--------- HR | Jamuna HR | Priya IT | Anita IT | Ganesh IT | Mahesh IT | Supriya Testing | Nita Testing | Priya Testing | Shilpa Testing | Supriya | Sangita |
Example 4: ORDER BY with Column Position
We can write the columns in the ORDER BY clause by specifying the position of a column in the SELECT clause, instead of writing the column name.
We can write above query using the position number of the column.
|
1 |
SELECT Department, Name FROM Employee ORDER BY 1,2; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Department | Name ------------+--------- HR | Jamuna HR | Priya IT | Anita IT | Ganesh IT | Mahesh IT | Supriya Testing | Nita Testing | Priya Testing | Shilpa Testing | Supriya |
If you check the above results you can clearly see that the rows are sorted on the first column i.e. Department and withing that group of rows of the same department, they are sorted based on Name.
Example 5: Use of Expressions in the ORDER BY Clause.
Aliases used in the SELECT Statement can be used in ORDER BY Clause.
|
1 |
SELECT Name, Salary, Salary * 0.1 as Bonus_sal FROM Employee ORDER BY Bonus_sal DESC; |
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Name | Salary | Bonus_sal --------+--------+----------- Jamuna | 85800 | 8580.0 Supriya | 66000 | 6600.0 Supriya | 60000 | 6000.0 Shilpa | 50000 | 5000.0 Priya | 42000 | 4200.0 Priya | 40000 | 4000.0 Ganesh | 30800 | 3080.0 Sangita | 30000 | 3000.0 Nita | 30000 | 3000.0 Mahesh | 18000 | 1800.0 Anita | 10000 | 1000.0 |
Example 6: Use of ASC and DESC keyword at the same time
Here we are applying ASC and DESC keyword at a time in a query.
Displaying departments in Ascending order and the employees under the departments in Descending order.
SELECT Department, Name FROM Employee ORDER BY Department ASC, Name DESC;
OUTPUT:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Department | Name -------------+--------- HR | Priya HR | Jamuna IT | Supriya IT | Mahesh IT | Ganesh IT | Anita Testing | Supriya Testing | Shilpa Testing | Priya Testing | Nita | Sangita |
In the next article of this series, we will see GROUP BY clause.
Tutorial Index:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
