ORDER BY in SQL

In the world of SQL, the ORDER BY clause is a powerful tool for organizing retrieved data in a specific sequence. This article delves into the nuances of utilizing ORDER BY, offering insights into its default ascending order sorting, and how to manipulate data presentation using DESC for descending and ASC for ascending order. Let's explore the essential rules and versatile applications of the ORDER BY statement in SQL, enhancing your database querying skills.

Syntax

• Sort according to one column

We use ASC and DESC keywords to sort a column in ascending and descending order, respectively.

• Sort according to multiple columns

Multiple columns are sorted simultaneously by separating their names by the comma(,) operator. The names of the two columns must be unique. The records are first sorted by the first column and then the sorted list by the second column if two rows had some value earlier.

• Sort the records in ascending order

ASC keyword is used to sort the records in ascending order.

• Sort the records in descending order

DESC keyword is used to sort the records in descending order.

• Sort the records in ascending order without using the ASC keyword

When ASC or DESC keyword is not specified, the ORDER BY clause sorts the records in ascending order.

Demo Database

Consider the table Employees having the following records:

ORDER BY with ASC

The ORDER BY clause in SQL plays a crucial role in organizing the retrieved data from a database in a specified order. When used with the ASC keyword, it sorts the data in ascending order based on one or more specified columns.

Syntax:

Example: Using the Employees table, let's sort the data by the SALARY in ascending order:

Output:

This result set shows all employees sorted by their salaries from the lowest to the highest.

ORDER BY with DSC

While the default behavior of ORDER BY is to sort data in ascending order, using the DESC keyword explicitly instructs SQL to sort the data in the opposite direction, providing a top-down view of your dataset.

Syntax:

Example: Consider you want to sort the employees from the provided table based on their salary in descending order to quickly identify the highest earners.

Output: This query will sort the "Employees" table by the "SALARY" column in descending order, showing the employee with the highest salary first.

This example demonstrates the effectiveness of using ORDER BY with DESC in sorting data from the highest to the lowest, facilitating quick insights into the dataset based on the specified column.

ORDER BY Clause on Single Column

Syntax:

Example: Consider you want to sort the employees from the Employees table by their SALARY in ascending order. The query would be:

Output: The output will list all employees sorted by their salaries in ascending order.

Sorting by a single column is straightforward and is a fundamental aspect of SQL that enhances data readability and management.

ORDER BY Clause on Multiple Columns

Syntax The syntax for using the ORDER BY clause on multiple columns is straightforward:

Example: Consider you want to sort the "Employees" table first by SALARY in descending order and then by NAME in ascending order. The query would look like this:

Output: This query will first sort the employees by their salaries in descending order. If two employees have the same salary, their names will be sorted in ascending order. Here's how the sorted table might look:

ORDER BY with WHERE Clause

In SQL, the combination of the ORDER BY and WHERE clauses is a powerful tool that allows for the precise retrieval and sorting of data from a database.

Syntax

Example To illustrate, let's consider a query on the Employees table where we want to retrieve details of employees who earn more than 2000.00, sorted by their age in ascending order.

Output This query filters out employees earning over 2000.00 and then sorts the result by their age in ascending order. Given the Employees table provided, the output would be:

ORDER BY with LIMIT Clause

In SQL, combining the ORDER BY clause with the LIMIT clause allows for powerful data retrieval operations, enabling not only sorting of data but also specifying the exact number of records to fetch.

Syntax:

Query: To demonstrate, let's fetch the top 3 highest earning employees from our Employees table:

Output: This query sorts the employees in descending order based on their salaries and limits the output to the top 3 records.

Sorting Results in a Preferred Order/Create a unique query

In SQL, sorting the results of a query in a specific order is crucial for organizing and presenting data in a meaningful way.

Syntax The syntax for sorting by column number instead of column name is straightforward. You use the ORDER BY clause followed by the position number(s) of the column(s) in the SELECT list. Here's a generic example:

Example Given the "Employees" table, suppose you want to sort the results first by AGE (third column) and then by SALARY (fifth column), without directly referencing the column names. The query would be:

Output Sorting the "Employees" table by AGE and then by SALARY yields the following ordered list: