In This Tutorial
MySQL Order by clause
A few important points about Order by clause of MySQL
- To return the result set in ascending or descending order, you can use the Order by clause of MySQL.
- The Order by clause is used with the Select statement.
- After the order by keyword, you have to specify one or more fields/columns of the table on the basis of what results will be sorted.
- The default is ascending order, however, you can use ASC as well, to sort the results in ascending order.
- To sort results by descending order use DESC keyword.
Order by MySQL syntax
This is how you can use order by clause in MySQL:
Select * from table_name
Where col1=val1, col2=val2
Order by some_column_in_table
MySQL Order by example
To explain order by clause with examples, we have created a tbl_employee table in our test database of MySQL. The table contains five columns. You can see how that table was created by using the Create Table query.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Create table tbl_employee
(emp_id int,
emp_name varchar(100),
emp_age int,
emp_salary decimal(8,2),
joining_date datetime,
PRIMARY KEY (emp_id)
);
|
An order by example with single column in ASC order
The following example will return all records from tbl_employee table. In the order by clause, we have used emp_name column to show results in ascending order.
The order by query:
|
1
2
3
|
select * from tbl_employee
order by emp_name ASC
|
An example of order by in DESC order
The following is an example to use order by clause with DESC keyword. The sorting is on employee name column and it will sort the results in DESC order. That means employee name started by Z will be shown first and A in the last.
MySQL order by query will be:
|
1
2
3
|
select * from tbl_employee
order by emp_name DESC
|
Example of MySQL sort by multiple columns
As we mentioned earlier, you can use more than one columns in the order by clause. Keep in mind that order by clause will first sort results on the basis of the first column, then second and so on. So the sequence of using columns in the order by clause is important.
For example, in our employees table we need to sort the result by emp_name and joining_date columns in ASC order. The query will first get all records started by ‘A’. Suppose we have five records starting with A. Now these five records will be sorted by second column, i.e. the employee who joined earlier will be shown first. After sorting five records by two columns, the pointer will move to next letter B and so on.
See following example where we will sort by two columns:
The order by query of MySQL with two column names:
|
1
2
3
|
select * from tbl_employee
order by joining_date,emp_name
|
Also see MySQL select | MySQL Like
Leave A Comment?