Introduction

Structured Query Language (SQL) is a powerful tool for managing and manipulating databases. One of its key features, the ORDER BY clause, allows users to sort query results. In this article, we'll delve into the intricacies of using multiple columns with the ORDER BY clause to refine and organize your data effectively.

What is SQL ORDER BY?.

The ORDER BY clause in SQL is used to sort the results of a query in ascending (ASC) or descending (DESC) order based on one or more columns. This is crucial for presenting data in a meaningful way, especially when dealing with large datasets.

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

• column_name1, column_name2, ..., column_nameN: Represents the column names.

Sorting in Ascending Order

Let's start with a fundamental scenario – sorting data in ascending order based on a single column.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

Sorting in Ascending Order

Similarly, we can sort data in descending order.

SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;

Now, let's explore sorting by more than one column, which is a more advanced and detailed way of using the ORDER BY clause.

Ascending Order for Multiple Columns

SELECT column1, column2, column3
FROM table_name
ORDER BY column1 ASC, column2 ASC, column3 ASC;

Descending Order for Multiple Columns

Conversely, sorting in descending order with multiple columns.

SELECT column1, column2, column3
FROM table_name
ORDER BY column1 DESC, column2 DESC, column3 DESC;

Optimizing Queries with Conditions

To further refine your results, consider integrating the WHERE clause for targeted sorting.

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC;

Real-world Examples

Consider the table(s) below as an example to use when creating the SQL query for getting the results you want.

cus_id	cus_name	amount	lane_id	cus_type
001	Customer1	45000.00	1000	WholeSale
002	Customer2	40000.00	2000	Retail
003	Customer3	27000.00	1000	WholeSale
004	Customer4	25000.00	2000	Retail
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
007	Customer7	15000.00	1000	WholeSale
008	Customer8	14000.00	2000	Retail
009	Customer9	13000.00	2000	WholeSale

1. Sorting Customers by customer type and amount

Imagine organizing a list of Customers by their customer type and amount. The query would look like this:

SELECT cus_name, cus_type, amount, lane_id
FROM customer_details
ORDER BY cus_type ASC, amount DESC;

By executing above query, we can get results as shown below –

cus_id	cus_name	amount	lane_id	cus_type
002	Customer2	40000.00	2000	Retail
004	Customer4	25000.00	2000	Retail
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
008	Customer8	14000.00	2000	Retail
001	Customer1	45000.00	1000	WholeSale
003	Customer3	27000.00	1000	WholeSale
007	Customer7	15000.00	1000	WholeSale
009	Customer9	13000.00	2000	WholeSale

2. Sorting Employees by Designation and Department ID

SELECT cus_name, lane_id, cus_type
FROM customer_details
ORDER BY cus_type ASC, lane_id ASC;

By executing above query, we can get results as shown below –

cus_id	cus_name	amount	lane_id	cus_type
005	Customer5	20000.00	1000	Retail
006	Customer6	18000.00	1000	Retail
002	Customer2	40000.00	2000	Retail
004	Customer4	25000.00	2000	Retail
008	Customer8	14000.00	2000	Retail
001	Customer1	45000.00	1000	WholeSale
003	Customer3	27000.00	1000	WholeSale
007	Customer7	15000.00	1000	WholeSale
009	Customer9	13000.00	2000	WholeSale