- 4 years ago
- Zaid Bin Khalid
- 2321 Views
-
2
In this session, you will learn how to use SQL ORDER to find out the result in a query of one or more columns.
SQL ORDER BY Clause.
The SQL ORDER BY clause is used to sort the resulting data in ascending or descending order based on the values returned by an SQL query.
Ordering the Resultset.
The order of rows in the output result when you use the SELECT statement in an SQL query the result is not guaranteed. This is because the SQL server returns a result with an unspecified order of rows. Hence, the only and best way you can specify the output result is to use the ORDER BY clause at the end of the statement. This way it tells the server about, how to sort the data returned by the query.
Typically, the default sorting order is ascending.
ORDER BY clause Syntax.
SELECT column_list FROM table_name ORDER BY column_name ASC|DESC;
Now, we will use the employee’s table in the database for the demonstration.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
+--------+--------------+------------+--------+---------+
Sorting Single Column.
Following SQL Statement used to return the data of all the employee’s from the employee’s table and orders the result set in ascending order by the emp name.
SELECT * FROM employees ORDER BY emp_name ASC;
Based on your choice, you can skip the ASC option and use the following syntax. It will return the same result set. The default sorting order is ascending.
SELECT * FROM employees ORDER BY emp_name;
After the execution of the above statement in SQL, your result will be expressed as.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
+--------+--------------+------------+--------+---------+
Similarly, if you want to perform sorting in descending order, you can use the DESC option. This statement will order the result set by the numeric salary column in descending order.
SELECT * FROM employees ORDER BY salary DESC;
At this time the execution of above statement in SQL, your result will be expressed as:
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
+--------+--------------+------------+--------+---------+
Sorting Multiple Columns.
You can also specify multiple columns while sorting the data. Though, the change in the result set will not become visible until you have some duplicate values within your table.
Let’s suppose, we have a trainee table in our database that includes the following database.
+----+------------+------------+-------------+--------+
| id | first_name | last_name | birth_date | gender |
+----+------------+------------+-------------+--------+
| 1 | Peter | Parker | 1998-03-04 | M |
| 2 | Harry | Potter | 2001-08-30 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 5 | John | Connor | 2002-01-15 | M |
+----+------------+------------+-------------+--------+
If you see the table carefully, It will show you that you some duplicate values in the table. However, the full names of the trainee “Peter Parker” and “Peter Pan” are different, but their first names are the same.
Let execute the following command, which orders the result set by the first name column.
SELECT * FROM trainees ORDER BY first_name;
After execution of above statement in SQL, your result will be expressed as.
+----+------------+------------+-------------+--------+
| id | first_name | last_name | birth_date | gender |
+----+------------+------------+-------------+--------+
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 2 | Harry | Potter | 2001-08-30 | M |
| 5 | John | Connor | 2002-01-15 | M |
| 1 | Peter | Parker | 1998-03-04 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
+----+------------+------------+-------------+--------+
Now let’s execute the statement, which orders the result set of the table by column name (first_name and last_name)
SELECT * FROM trainees ORDER BY first_name, last_name;
+----+------------+------------+-------------+--------+
| id | first_name | last_name | birth_date | gender |
+----+------------+------------+-------------+--------+
| 4 | Alice | Kingsleigh | 1999-07-02 | F |
| 2 | Harry | Potter | 2001-08-30 | M |
| 5 | John | Connor | 2002-01-15 | M |
| 3 | Peter | Pan | 2004-09-19 | M |
| 1 | Peter | Parker | 1998-03-04 | M |
+----+------------+------------+-------------+--------+
This time you will notice the difference between the previous and the current output sets. In this query, the output record of the trainee table will return the names in such an order where “Peter Parker” comes after the “Peter Pan“.
- 4 years ago
- Zaid Bin Khalid
- 2321 Views
-
2