SQL is a language used to manage and manipulate relational databases. One of the most common operations performed on a database is sorting data. The SQL ORDER BY keyword is used to sort the data in a specific order. The data can be sorted either in ascending or descending order based on one or multiple columns. The sorted data can be used to generate reports or to analyze trends in the data.
In this article, we will be discussing the usage of the ORDER BY keyword and its syntax with examples.
Syntax
The basic syntax for the ORDER BY keyword is as follows:
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
SELECT is used to select the columns from the table. FROM is used to specify the table from which the data is being selected. The ORDER BY keyword is used to sort the data. The columns specified after ORDER BY are the columns that the data is being sorted by. The optional ASC or DESC keyword specifies whether the data is sorted in ascending or descending order. By default, the data is sorted in ascending order if the ASC or DESC keyword is not specified.
Sample Data
Consider the following sample data in a table named orders:
| Order ID | Customer Name | Order Date | Amount |
|---|---|---|---|
| 1 | John Doe | 2022-01-01 | 100 |
| 2 | Jane Doe | 2022-01-03 | 200 |
| 3 | John Doe | 2022-01-02 | 150 |
| 4 | Jane Doe | 2022-01-04 | 250 |
Examples
Sort by a single column
Consider the following example to sort the data in the orders table by the Amount column in ascending order:
SELECT * FROM orders ORDER BY Amount;
The output of the above query would be:
| Order ID | Customer Name | Order Date | Amount |
|---|---|---|---|
| 1 | John Doe | 2022-01-01 | 100 |
| 3 | John Doe | 2022-01-02 | 150 |
| 2 | Jane Doe | 2022-01-03 | 200 |
| 4 | Jane Doe | 2022-01-04 | 250 |
The data is sorted in ascending order based on the Amount column. The lowest value of 100 is displayed first, followed by 150, and so on.
Here’s an example of sorting by DESC:
SELECT * FROM orders ORDER BY Amount DESC;
This query would return the rows from the orders table sorted in descending order based on the Amount column. The row with the highest value in the Amount column would be displayed first, followed by the next highest value, and so on.
For example:
| Order ID | Customer Name | Order Date | Amount |
|---|---|---|---|
| 4 | Jane Doe | 2022-01-04 | 250 |
| 2 | Jane Doe | 2022-01-03 | 200 |
| 3 | John Doe | 2022-01-02 | 150 |
| 1 | John Doe | 2022-01-01 | 100 |
Sort by multiple columns
Consider the following example to sort the data in the orders table by the Customer Name column in ascending order, and then by the Amount column in descending order:
SELECT * FROM orders ORDER BY Customer Name, Amount DESC;
The output of the above query would be:
| Order ID | Customer Name | Order Date | Amount |
|---|---|---|---|
| 1 | John Doe | 2022-01-01 | 100 |
| 3 | John Doe | 2022-01-02 | 150 |
| 2 | Jane Doe | 2022-01-03 | 200 |
| 4 | Jane Doe | 2022-01-04 | 250 |
The data is first sorted in ascending order based on the Customer Name column. Within each customer name, the data is then sorted in descending order based on the Amount column.
Conclusion
The ORDER BY clause is an essential part of the SELECT statement in SQL. It allows you to sort the data in a specific order, either ascending or descending, based on one or multiple columns. The sorted data can be displayed in a table format and is useful for finding specific information and analyzing data in a meaningful way. By using the ORDER BY clause, you can quickly and easily sort data based on your specific needs.
