YouTipPostgresql Order By
# PostgreSQL ORDER BY Clause
In PostgreSQL, the **ORDER BY** clause is used to sort the data returned by a query in either ascending (ASC) or descending (DESC) order, based on one or more columns.
By default, if you do not specify a sorting order, PostgreSQL will return rows in an unspecified order (which typically depends on how the data is physically stored on disk). Using `ORDER BY` ensures a consistent, predictable presentation of your query results.
---
## Syntax
The basic syntax of the `ORDER BY` clause is as follows:
```sql
SELECT column_list
FROM table_name
[ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... columnN [ASC | DESC]];
```
### Key Parameters:
* **`column1, column2, ... columnN`**: The columns by which you want to sort the result set. These columns must exist in the table or be valid expressions in the `SELECT` list.
* **`ASC`**: Sorts the data in ascending order (lowest to highest, A to Z). This is the **default** behavior if no direction is specified.
* **`DESC`**: Sorts the data in descending order (highest to lowest, Z to A).
---
## Demo Database Setup
To demonstrate how the `ORDER BY` clause works, we will use a sample table named `COMPANY`.
First, let's create the table and populate it with some sample data:
```sql
-- Create the COMPANY table
CREATE TABLE COMPANY (
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
salary REAL
);
-- Insert sample records
INSERT INTO COMPANY (id, name, age, address, salary) VALUES
(1, 'Paul', 32, 'California', 20000),
(2, 'Allen', 25, 'Texas', 15000),
(3, 'Teddy', 23, 'Norway', 20000),
(4, 'Mark', 25, 'Rich-Mond', 65000),
(5, 'David', 27, 'Texas', 85000),
(6, 'Kim', 22, 'South-Hall', 45000),
(7, 'James', 24, 'Houston', 10000);
```
If we query the table without any sorting, the default output looks like this:
```sql
runoobdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
(7 rows)
```
---
## Code Examples
### 1. Sorting by a Single Column in Ascending Order (ASC)
The following query sorts the records in ascending order based on the `age` column:
```sql
runoobdb=# SELECT * FROM COMPANY ORDER BY age ASC;
```
**Output:**
```text
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
6 | Kim | 22 | South-Hall | 45000
3 | Teddy | 23 | Norway | 20000
7 | James | 24 | Houston | 10000
4 | Mark | 25 | Rich-Mond | 65000
2 | Allen | 25 | Texas | 15000
5 | David | 27 | Texas | 85000
1 | Paul | 32 | California | 20000
(7 rows)
```
*Note: Since `ASC` is the default sorting order, `ORDER BY age` would yield the exact same result.*
---
### 2. Sorting by Multiple Columns
You can sort by multiple columns. PostgreSQL will first sort the result set by the first column specified. If there are duplicate values in that column, it will resolve the order using the second column, and so on.
The following query sorts the records by `name` in ascending order, and then by `salary` in ascending order:
```sql
runoobdb=# SELECT * FROM COMPANY ORDER BY name, salary ASC;
```
**Output:**
```text
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
2 | Allen | 25 | Texas | 15000
5 | David | 27 | Texas | 85000
7 | James | 24 | Houston | 10000
6 | Kim | 22 | South-Hall | 45000
4 | Mark | 25 | Rich-Mond | 65000
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
(7 rows)
```
---
### 3. Sorting by a Column in Descending Order (DESC)
The following query sorts the records in descending alphabetical order based on the `name` column:
```sql
runoobdb=# SELECT * FROM COMPANY ORDER BY name DESC;
```
**Output:**
```text
id | name | age | address | salary
----+-------+-----+----------------------------------------------------+--------
3 | Teddy | 23 | Norway | 20000
1 | Paul | 32 | California | 20000
4 | Mark | 25 | Rich-Mond | 65000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
5 | David | 27 | Texas | 85000
2 | Allen | 25 | Texas | 15000
(7 rows)
```
---
## Advanced Considerations
### Sorting with NULL Values
In PostgreSQL, `NULL` values are treated as larger than any non-null value.
* By default, in an **ascending (`ASC`)** sort, `NULL` values appear at the **end**.
* By default, in a **descending (`DESC`)** sort, `NULL` values appear at the **beginning**.
You can explicitly control where `NULL` values are placed using `NULLS FIRST` or `NULLS LAST`:
```sql
-- Force NULL values to the beginning of an ascending sort
SELECT * FROM COMPANY ORDER BY salary ASC NULLS FIRST;
-- Force NULL values to the end of a descending sort
SELECT * FROM COMPANY ORDER BY salary DESC NULLS LAST;
```
### Sorting by Column Position
Instead of typing the column names, you can refer to columns by their ordinal position in the `SELECT` list (starting from 1):
```sql
-- Sorts by the 2nd column (name) in ascending order
SELECT id, name, age FROM COMPANY ORDER BY 2 ASC;
```