How To Use Order By in MySQL

Introduction

Order by is a most useful feature in MySQL that allows you to sort query results based on one or more columns in ascending or descending Order. In the article, we will discuss how to use Order in MySQL with step-by-step examples.

Step 1. Create a table and insert data To demonstrate the use of Order by. We need to create a table and insert some data into it. Here is an example of creating a table called "students" with some sample data.

CREATE TABLE students ( 
id INT PRIMARY KEY, 
name VARCHAR(50), 
age INT, grade CHAR(1) ); 
INSERT INTO students (id, name, age, grade)
VALUES (1, 'John Doe', 25, 'A'), 
(2, 'Jane Smith', 23, 'B'), 
(3, 'Tom Brown', 28, 'C'), 
(4, 'Mary Johnson', 21, 'A'), (5, 'Peter Lee', 26, 'B');

Output

Step 2. Use Order by clause To sort the rows in the "students" table by name in ascending Order, we can use the ORDER BY clause as follows.

SELECT * FROM students ORDER BY name ASC;
SELECT * FROM students ORDER BY name DESC;

Output

The above query will order the rows in the "students" table by name in ascending Order.

Step 3. Use Order by with multiple columns. We can also sort the rows in the "students" table by multiple columns. For example, if we want to sort the rows by grade in descending Order and then by age in ascending Order, we can use the following query.

SELECT * FROM students ORDER BY grade DESC, age ASC;

Output

The above query will order the rows in the "students" table by grade in descending Order and then by age in ascending Order.

Step 4. Use Order by with aggregate functions. In addition to sorting by columns, we can also sort the rows in a query result based on the result of an aggregate function. For example, if we want to find the average age of students in each grade and then sort the grades by average age in ascending Order, we can use the following query.

SELECT grade, AVG(age) AS avg_age FROM students GROUP BY grade ORDER BY avg_age ASC;

Output 

The above query will group the students by grade, calculate the average age for each grade using the AVG() function, and then order the grades by average age in ascending Order.

Step 5. Use Order by with NULL values. By default, NULL values are treated as the lowest possible value when using ORDER BY. However, you can use the NULLS FIRST or NULLS LAST options to specify whether NULL values should be sorted first or last in the result set. For example, if we want to sort the rows in the "students" table by age in descending Order but treat NULL values as the highest possible value, we can use the following query.

SELECT grade, AVG(age) AS avg_age FROM students GROUP BY grade ORDER BY avg_age ASC;

The above query will order the rows in the "students" table by age in descending Order, with NULL values sorted last using MySQL.

Conclusion

The Order by clause is a useful feature in MySQL that allows you to sort query results based on one or more columns in ascending or descending Order. By using Order by, you can easily sort and organize large amounts of data. With the help of the examples provided above, you should now have a good understanding of how to use (Order by) in MySQL.