MySQL ORDER BY Keyword

Creating Sample Dataset

Let us try to create a dataset on which we will try different methods to use the ORDER BY Keyword. 

-- Creating an Employees Table
CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  age INT,
  salary DECIMAL(10,2),
  hire_date DATE
);

-- Inserting the values in the Employees table
INSERT INTO employees (id, first_name, last_name, age, salary, hire_date)
VALUES
  (1, 'Ram', 'Patel', 30, 50000.00, '2022-01-15'),
  (2, 'Odean', 'Smith', 35, 60000.00, '2021-08-20'),
  (3, 'Mary', 'Johnson', 28, 45000.00, '2023-02-10'),
  (4, 'Sachin', 'Tichkule', 40, 70000.00, '2020-11-05'),
  (5, 'Dani', 'Davis', 32, 55000.00, '2022-06-30'),
  (6, 'Leah', NULL, 28, NULL, '2023-02-10'),
  (7, NULL, 'Smith', NULL, 65000.00, '2022-09-15'),
  (8, 'John', 'Doe', NULL, NULL, NULL);

Employee Table

ORDER BY Attributes

There are several attributes of ORDER BY Keyword in MySQL, which we use to sort the data according to priority.

• Ascending (ASC) attribute sorts data in ascending order (from lowest to highest). It is the default Sorting order if any attribute is not mentioned.
   
• Descending (DESC) attribute sorts data in descending order (from highest to lowest).
   

Note: While sorting in ascending order, NULL values always come up first, and while sorting in descending order, NULL value rows come up in the last order. 

Different Methods to use ORDER BY

Let us look at the different methods to use ORDER BY Keyword in MySQL.

Sorting by Single Column

Firstly let us look at the general syntax to sort a table by a single column.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];

Example 1:

-- Sorting the table by column last_name in ascending order.
SELECT *
FROM employees
ORDER BY last_name ASC;

Output

Explanation

Here in this method, we have sorted the table according to last_name priority in ascending order.

Example 2:

-- Sorting the table by column last_name in descending order.
SELECT *
FROM employees
ORDER BY last_name DESC;

Output

Explanation

Here in this method, we have sorted the table according to last_name priority in descending order.

Sorting by Multiple Columns

Firstly let us look at the general Syntax to sort a table by multiple columns.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...;

Example

-- Sorting First Name in ascending order and salary in descending order.
SELECT *
FROM Employees
ORDER BY first_name ASC, salary DESC;

Output

Explanation

In this method, firstly, we have sorted the table according to the first_name column, and also, there are no rows containing similar first_name, so the need for sorting using the salary column is not there in this case.

Sorting using Expressions

In MySQL, we can use functions, calculations or expressions to sort the data using MySQL Keyword.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY expression1 [ASC|DESC], expression2 [ASC|DESC], ...;

Example

-- Sorting the employees table using the expression of Salary*age.
SELECT *
FROM employees
ORDER BY salary * age DESC;

Output

Explanation

In this method, we have sorted the table according to the expression of multiplying salary and age (salary*age) in descending order. Assuming NULL values as zero, they will take up the last positions in the table.

Sorting using Custom List

The FIELD() function in MySQL is used to get the index position of an element in a list. 

In MySQL, we can also use the FIELD() function in ORDER BY Keyword to sort the data using a custom list provided.

Syntax 

SELECT column1, column2, ...
FROM table_name
ORDER BY FIELD(column_name, value1, value2, value3, ...) ASC|DESC;

Example

-- Sorting the employees table using a custom first_name list.
SELECT *
FROM employees
ORDER BY FIELD(first_name, 'Ram', 'Dani', 'Mary', 'Leah', 'Odean', 'Sachin', 'John');

Output

Explanation

In this method, we have been provided with the custom list of the first_name column, so the table has been sorted according to the custom list. 

Sorting using GROUP BY And Aggregate Functions

The GROUP BY clause groups the rows with the same values in a new summary row.

We can also use ORDER BY Keyword with GROUP BY clause and aggregate functions in MySQL. This allows us to order the groups based on the calculated results.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
ORDER BY aggregate_function(column2) [ASC|DESC];

Example

-- Sorting the employees table using GROUP BY clause on age and Count function in descending order.
SELECT age, COUNT(*) AS Count
FROM employees
GROUP BY age
ORDER BY COUNT(*) DESC;

Output

Explanation

In this method, we have sorted the data according to the age count in descending order. We can see that the age of 28 has a count of two, so it comes up first, and the rest of the data with the same count is sorted in ascending order by default.

Sorting by Date and Time

We can also sort the data according to the data and time in MySQL using the ORDER BY Keyword, which sorts our data depending on the date column.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY date_column [ASC|DESC];

Example

-- Sorting the employees table by the date a certain employee was hired.
SELECT *
FROM employees
ORDER BY hire_date ASC;

Output

Explanation

In this method, the table is sorted according to the employee's hiring date in ascending order.

Sorting with Limit

We can also limit the number of rows to see the top rows in the sorted data. This helps us take a quick look at the data and decide accordingly.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number_of_rows;

Example

-- Sorting the Employees table on the column Salary and limiting the number of rows to 3.
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 3;

Output

Explanation

Here in this method, firstly, we have sorted the data according to the employee's salary and used the Limit function to view the top three salary rows.

Advantages

Here are a few advantages of using ORDER BY Keyword in MySQL.

• It helps to sort the data in a specific order, making it more presentable and easy to view.
   
• ORDER BY Keyword is also customisable. We can sort the data according to the priority we set.
   
• As the data is sorted using ORDER BY Keyword, this increases user interaction and experience.
   
• ORDER BY keyword helps in data analysis to track the patterns or examine any trend.

Disadvantages

Here are some of the disadvantages we need to keep in mind before using ORDER BY Keyword.

• Performing sorting operations on large datasets takes more time which may affect the responsiveness of our application.
   
• Upon working with large datasets, ORDER BY Keyword decreases the performance of the query.
   
• It can also take up a lot of memory if the size of the dataset is large.

Frequently Asked Questions

Why do we use the ORDER BY Keyword in MySQL?

In MySQL, the ORDER BY Keyword is used to sort the data according to a priority set by the user. 

Can we sort the data in both ascending and descending order?

Yes, we can sort the data in ascending and descending order using ASC or DESC keywords, respectively.

How do we sort the table based on multiple columns?

We can use the ORDER BY clause separated by commas to sort based on multiple columns.

Can we sort the data using expressions or calculations?

Yes, the ORDER BY clause can be used to sort the data using expressions or calculations.

How does the ORDER BY Keyword affect query performance?

While sorting large datasets using the ORDER BY Keyword affects impact performance, especially for complex queries.

Conclusion

This article discusses the topic of MySQL ORDER BY Keyword. In this blog, we have discussed how to use MySQL ORDER BY Keyword, including the ways to use it in MySQL.

We hope this blog has helped you enhance your knowledge of ORDER BY Keyword in MySQL. If you want to learn more, then check out our articles.

• What is View in SQL
• MySQL: Case Statement
• MySQL Variables
   

And many more on our platform Coding Ninjas Studio.

Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System Design, and many more! If you want to test your coding ability, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. In that case, you must look at the problems, interview experiences, and interview bundles for placement preparations.

However, you may consider our paid courses to give your career an edge over others!

Happy Learning!