Learn to use the MySQL ORDER BY Clause with the help of programming examples in this hands-on tutorial:
MySQL ORDER BY is a command or clause that is generally used along with SELECT Queries, to SORT the returned result set in ascending or descending order.
The command is widely implemented and is very useful as it can be used along with other options like LIMIT and JOIN etc to further reduce the result set and return the required results.
Further, there can be multiple ORDER BY clauses used in the same table and different sort orders can be applied to each of those.
=> Check All MySQL Tutorials Here
Table of Contents:
MySQL ORDER BY Clause
Test Data
-- table creation - employee_department
CREATE TABLE employee_department(dept_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(100));
-- table creation - employees
CREATE TABLE employees (emp_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(150) DEFAULT NULL,
dept_id SMALLINT, CONSTRAINT deptIdFk FOREIGN KEY(dept_id)
REFERENCES employee_department(dept_id) ON UPDATE CASCADE ON DELETE CASCADE);
-- data insertion employee_department
INSERT INTO employee_department(dept_name) VALUES ("HR"),("ENGINEERING"),("SALES"),("MARKETING");
-- data insertion employees
INSERT INTO employees(emp_name, dept_id) VALUES
("Damian Kim",1),("Tara Vargas",2),("Marlon Jenning",4),("Leopold Schuman",2),("Bailey Sutton",3),("Toby Roffe",4),("Troy Duncan", 1),("Polly Berry",2),("Dorian Maillin", 2),("Thora Mason",3),("Taylor Morales",4),("Ida Carr",2),("Prateek Pal",2),("Janya Sood",4),("Tara Shetty",2),("Padma Krishna",1);
Syntax
SELECT {column list}
FROM {tableName}
ORDER BY
{column1 ASC|DESC},
{column2 ASC|DESC},
...
Let’s understand the different parts of the syntax:
- {column-list}: Refers to the columns required in the result set.
- {table-name}: Is the table being queried.
- {column1 ASC|DESC}: The column name against which the result set needs to be sorted. ASC refers to ASCENDING sort and DESC refers to Descending Sort.
- We can specify multiple columns for ORDER BY clauses and specify different sort orders as per the requirement.
Note:
By default, the sort order for MySQL ORDER BY is ASCENDING.
MySQL ORDER BY uses the existing column indexes if available for the columns that are mentioned in the ORDER BY clauses.
MySQL ORDER BY Examples
#1) Simple ORDER BY
SELECT * FROM employees ORDER BY emp_name;
Here, you can see that we are doing a sort by emp_name. Also, as we have not specified any sort order, the default sort order assumed by MySQL engine is ASCENDING order. We can notice in the result set that the records are sorted in ascending order of the employee names.
Let’s try running this for DESCENDING order
SELECT * FROM employees ORDER BY emp_name DESC;
We can use the same query and just mention sort order as DESC.
Here’s the output with the employee names sorted in descending order.
#2) With LIMIT
Now let’s see how we can use the ORDER BY clause with the LIMIT option to limit the number of search results returned.
We would use the same query to sort by the employee name ascending and fetch the first 5 records.
SELECT * FROM employees ORDER BY emp_name LIMIT 5;
In this case, the MySQL Engine would sort the table with the sort order and return the first 5 records as mentioned in the LIMIT clause.
#3) With JOINS
Let’s see an example of using ORDER BY along with JOINS.
SELECT emp.emp_name, emp_dept.dept_name FROM employees emp INNER JOIN employee_department emp_dept ON emp.dept_id = emp_dept.dept_id ORDER BY emp.emp_name ASC
#4) ORDER BY With Multiple Columns
Let’s see an example of using multiple columns with ORDER BY clause and see how sorting is applied.
We will use the previous query with additional column dept_name ordered by in ASCENDING and names within each department are sorted in DESCENDING order.
SELECT .emp_name, emp_dept.dept_name FROM employees emp INNER JOIN employee_department emp_dept ON emp.dept_id = emp_dept.dept_id ORDER BY empemp_dept.dept_name ASC, emp.emp_name DESC
In the above output, you can see that the rows are sorted in ASCENDING order of the department names. The employee names within the departments are sorted in DESCENDING order.
For example: Refer HR department – the names appear in the order TROY, PADMA, and DAMIAN – which is DESCENDING ALPHABETICAL order for column emp_names.
#5) MySQL ORDER BY RAND()
MySQL ORDER BY can be used with the RAND() option to get random records from the table.
This option does not expect a column name after the ORDER BY clause and is usually used along with the LIMIT statement to return a given no of rows.
For example: Suppose you want to fetch 2 random records from the employees table, then you can execute the below query.
SELECT * FROM employees ORDER BY RAND() LIMIT 2
Every time you run the above query, you will get different random results.
For Example, running the query 2 times, returned the result set as shown below:
Typical applications of using ORDER BY RAND() can be:
- Fetching random records for understanding column schema and the type of data that the table holds.
- Specific applications like doing a lucky draw from a table having 1 million records – fetch 3 random rows.
Frequently Asked Questions
Q #1) How do I sort in MySQL?
Answer: MySQL provides the sort functionality using the ORDER BY clause. The default sort order is Ascending (or we can explicitly mention ASC for ascending to make queries readable) and for Descending sort, DESC keyword can be used.
Let’s have a look at simple queries to sort data.
We can use the below query to fetch the first 10 records sorted in descending order by their age from a table named employees having a column named emp_age.
SELECT * FROM employees ORDER BY emp_age DESCENDING LIMIT 10
Q #2) What does ORDER BY do in MySQL?
Answer: ORDER BY is a clause that is typically used along with SELECT queries in MySQL and is used to return the result set sorted in the given order against the given column or field in the table.
The ORDER BY clause can be used along with different options like:
Q #3) What’s the use of MySQL ORDER BY RAND()?
Answer: ORDER BY RAND() is a special clause that can be used to return random records from a given table. It’s generally used along with the LIMIT clause to specify the number of rows that need to be returned.
A sample query is below
SELECT * FROM employees ORDER BY RAND() LIMIT 4
The above query would result in returning 4 random records from the employee’s table.
Conclusion
In this tutorial, we learned about MySQL ORDER BY clause and the different options with which this clause can be used.
We also discussed MySQL ORDER BY RAND() which is typically used to return random records from a given table against the number of records specified by the LIMIT condition.
The ORDER BY clause is a widely used option in complex queries and web applications to retrieve and display data in the desired order.
=> Read Through The Simple MySQL Training Series