Using Order By in SQL with Examples

In this article, we will explore “Order by in SQL” using various examples. Let’s get started.

Table of Contents

What is the Purpose of Using Order By in SQL ?

The ORDER BY clause in SQL is used to sort the result set of a query in a specified order based on one or more columns. Its primary purpose is to provide control over the order in which the query results are presented. Here are the main purposes of using ORDER BY in SQL:

Sorting Data: The most common purpose of ORDER BY is to sort data in ascending (default) or descending order. This helps organize the data in a meaningful way, making it easier for users to read and analyze.
Ranking: In some cases, you might want to rank data based on certain criteria, such as displaying the top-performing products or employees. The ORDER BY clause, combined with functions like ROW_NUMBER(), can be used for ranking.
Pagination: When displaying large result sets in web applications or reports, ORDER BY is essential for implementing pagination. It ensures that the data is presented in a logical order, making it easier for users to navigate through pages.
Grouping: In combination with grouping functions like GROUP BY, ORDER BY can help arrange grouped data. For example, you can group sales data by month and then order the months chronologically.
Aggregations: When using aggregate functions like SUM, COUNT, or AVG, ORDER BY can be used to sort the aggregated results, allowing you to identify trends or outliers more easily.

Order By Clause Examples

Sorting Data using Order By in SQL

The most common purpose of ORDER BY in SQL is to sort data in ascending (default) or descending order. This helps organize the data in a meaningful way, making it easier for users to read and analyze.

Here are examples of SQL ORDER BY clause for sorting data in both ascending and descending orders:

Ascending Order:

-- Sorting products by price in ascending order
SELECT product_name, price
FROM products
ORDER BY price ASC;

This query will display products sorted by their prices in ascending order.

Descending Order:

-- Sorting products by price in descending order
SELECT product_name, price
FROM products
ORDER BY price DESC;

This query will display products sorted by their prices in descending order.

Order By Using Multiple Columns

Ordering data using multiple columns in SQL is a powerful way to specify complex sorting criteria. By using the ORDER BY clause with multiple columns, you can fine-tune how your data is sorted. Let’s explore how to do this with examples.

Suppose we have a sample table named orders with the following data:

+------+------------+-------------+
|  OrderID  |   Customer   |   OrderDate  |
+------+------------+-------------+
|  101     |   Alice     |   2023-01-15 |
|  102     |   Bob       |   2023-01-12 |
|  103     |   Carol     |   2023-01-15 |
|  104     |   David     |   2023-01-14 |
|  105     |   Eve       |   2023-01-13 |
|  106     |   Frank     |   2023-01-14 |
+------+------------+-------------+

Let’s explore examples of using the ORDER BY clause with multiple columns:

Example 1: Ordering by OrderDate Ascending, Then by Customer Ascending

SELECT * FROM orders
ORDER BY OrderDate ASC, Customer ASC;

In this query, we first order the data by the OrderDate column in ascending order. If two orders have the same date, they are further ordered by the Customer column in ascending order. The output will be:

+------+------------+-------------+
|  OrderID  |   Customer   |   OrderDate  |
+------+------------+-------------+
|  102     |   Bob       |   2023-01-12 |
|  105     |   Eve       |   2023-01-13 |
|  104     |   David     |   2023-01-14 |
|  106     |   Frank     |   2023-01-14 |
|  101     |   Alice     |   2023-01-15 |
|  103     |   Carol     |   2023-01-15 |
+------+------------+-------------+

Example 2: Ordering by Customer Descending, Then by OrderDate Ascending

SELECT * FROM orders
ORDER BY Customer DESC, OrderDate ASC;

In this query, we first order the data by the Customer column in descending order. If two orders have the same customer, they are further ordered by the OrderDate column in ascending order. The output will be:

+------+------------+-------------+
|  OrderID  |   Customer   |   OrderDate  |
+------+------------+-------------+
|  103     |   Carol     |   2023-01-15 |
|  101     |   Alice     |   2023-01-15 |
|  104     |   David     |   2023-01-14 |
|  106     |   Frank     |   2023-01-14 |
|  102     |   Bob       |   2023-01-12 |
|  105     |   Eve       |   2023-01-13 |
+------+------------+-------------+

Ranking

In some cases, you might want to rank data based on certain criteria, such as displaying the top-performing products or employees. The ORDER BY clause, combined with functions like ROW_NUMBER(), can be used for ranking.

Here’s an example of using the SQL ORDER BY clause and the ROW_NUMBER() function for ranking data:

Let’s assume we have a sample table called sales with the following data:

+----+------------+-------+
| id | order_date | total |
+----+------------+-------+
|  1 | 2023-01-05 | 100   |
|  2 | 2023-01-10 | 150   |
|  3 | 2023-02-15 | 200   |
|  4 | 2023-02-20 | 180   |
|  5 | 2023-03-10 | 220   |
|  6 | 2023-03-15 | 300   |
+----+------------+-------+

Now, let’s run the query to rank the sales data by the total amount in descending order:

SELECT id, order_date, total,
       ROW_NUMBER() OVER (ORDER BY total DESC) AS rank
FROM sales;

The output of this query will be:

+----+------------+-------+------+
| id | order_date | total | rank |
+----+------------+-------+------+
|  6 | 2023-03-15 | 300   |  1   |
|  5 | 2023-03-10 | 220   |  2   |
|  3 | 2023-02-15 | 200   |  3   |
|  4 | 2023-02-20 | 180   |  4   |
|  2 | 2023-01-10 | 150   |  5   |
|  1 | 2023-01-05 | 100   |  6   |
+----+------------+-------+------+

In this output, the data is sorted by the total column in descending order, and each row is assigned a rank based on the total amount. The row with the highest total amount receives a rank of 1, and the ranks increase as the total amount decreases.

Pagination

When displaying large result sets in web applications or reports, ORDER BY is essential for implementing pagination. It ensures that the data is presented in a logical order, making it easier for users to navigate through pages.

-- Implementing pagination for a list of articles
SELECT article_title, publication_date
FROM articles
ORDER BY publication_date DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This query fetches the first 10 articles sorted by publication date in descending order, which can be used for the first page of a pagination system.

Grouping and Aggregations

When using aggregate functions like SUM, COUNT, or AVG, ORDER BY can be used to sort the aggregated results, allowing you to identify trends or outliers more easily.

Let’s assume we have a sample table called product_sales with the following data:

+--------+---------------+
| category | sales_amount |
+--------+---------------+
| Electronics | 1200 |
| Clothing    | 800  |
| Electronics | 1500 |
| Furniture   | 700  |
| Clothing    | 600  |
| Furniture   | 900  |
+--------+---------------+

Now, let’s run the query to aggregate and order product sales by category:

SELECT category, SUM(sales_amount) AS total_sales
FROM product_sales
GROUP BY category
ORDER BY total_sales DESC;

The output of this query will be:

+--------------+-------------+
|   category   | total_sales |
+--------------+-------------+
| Electronics  |    2700     |
|   Clothing   |    1400     |
|  Furniture   |    1600     |
+--------------+-------------+

Conclusion : Order By Clause in SQL

In this article, we have explored the use of the “ORDER BY” clause in SQL through various examples. Here are the key points discussed:

Purpose of Using ORDER BY in SQL:
- Sorting Data: The primary purpose of the “ORDER BY” clause is to sort query results in either ascending or descending order, making data more readable and analyzable.
- Ranking: You can use the “ORDER BY” clause in combination with functions like ROW_NUMBER() to rank data based on specific criteria.
- Pagination: For web applications or reports with large datasets, the “ORDER BY” clause is essential for implementing pagination, ensuring logical data presentation.
- Grouping: When combined with grouping functions like GROUP BY, “ORDER BY” can arrange grouped data based on specified columns.
- Aggregations: When using aggregate functions like SUM, COUNT, or AVG, “ORDER BY” helps in sorting aggregated results, aiding in trend analysis.
Order By Clause Examples:
- Sorting Data:
  - Ascending Order: We provided an example of sorting products by price in ascending order.
  - Descending Order: We demonstrated how to sort products by price in descending order.
- Order By Using Multiple Columns:
  - Example 1: We showed how to order data by OrderDate in ascending order and then by Customer in ascending order.
  - Example 2: We explained how to order data by Customer in descending order and then by OrderDate in ascending order.
- Ranking:
  - We discussed how to use the SQL “ORDER BY” clause in combination with the ROW_NUMBER() function to rank data based on specific criteria. We provided an example and displayed the ranked output.
- Pagination:
  - We demonstrated how to implement pagination for a list of articles using the “ORDER BY” clause, OFFSET, and FETCH NEXT clauses.
- Grouping and Aggregations:
  - We illustrated how to aggregate and order product sales by category, showcasing the use of “GROUP BY” and “ORDER BY” for summarizing data.

In summary, the “ORDER BY” clause in SQL is a versatile tool for sorting, ranking, paginating, and summarizing data, making it an essential component for various data manipulation tasks in database queries.

Related Articles :