PostgreSQL ORDER BY: ASC vs DESC for Sorting Results

Hey guys! Let’s dive into how to use ORDER BY with ASC and DESC in PostgreSQL. If you’re working with databases, you know how important it is to sort your data in a meaningful way. PostgreSQL’s ORDER BY clause is your best friend here, allowing you to sort query results in ascending or descending order. We’ll cover everything you need to know, from basic syntax to more advanced techniques.

Understanding the Basics of ORDER BY

The ORDER BY clause in PostgreSQL is used to sort the rows returned by a SELECT statement. By default, if you don’t specify the order, PostgreSQL will return the rows in the order they were inserted, which isn’t very useful most of the time. The ORDER BY clause allows you to specify one or more columns to sort by. You can also specify the direction of the sort using ASC (ascending) or DESC (descending). Without specifying the order, ORDER BY defaults to ASC .

Syntax

The basic syntax looks like this:

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

Here’s what each part means:

• SELECT column1, column2, ... : Specifies the columns you want to retrieve.
• FROM table_name : Specifies the table you’re querying.
• WHERE condition : Filters the rows based on a condition (optional).
• ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... : Specifies the columns to sort by and the order (ascending or descending). You can sort by multiple columns, and each column can have its own sort order.

Example

Let’s say we have a products table with the following data:

id	name	price
1	Laptop	1200
2	Keyboard	75
3	Mouse	25
4	Monitor	300
5	Headphone	150

To sort the products by price in ascending order, you would use the following query:

SELECT id, name, price
FROM products
ORDER BY price ASC;

This would return:

id	name	price
3	Mouse	25
2	Keyboard	75
5	Headphone	150
4	Monitor	300
1	Laptop	1200

Notice that the products are now sorted from the lowest price to the highest price.

Sorting in Ascending Order (ASC)

The ASC keyword is used to sort the results in ascending order. This means from the lowest value to the highest value (for numeric columns), or from A to Z (for text columns). As mentioned earlier, if you omit the ASC or DESC keyword, PostgreSQL defaults to ascending order. Using ASC explicitly can make your queries more readable.

Example

Let’s sort the products table by product name in ascending order:

SELECT id, name, price
FROM products
ORDER BY name ASC;

This would return:

id	name	price
5	Headphone	150
2	Keyboard	75
1	Laptop	1200
4	Monitor	300
3	Mouse	25

The products are now sorted alphabetically by name.

Sorting in Descending Order (DESC)

The DESC keyword is used to sort the results in descending order. This means from the highest value to the lowest value (for numeric columns), or from Z to A (for text columns). Using DESC is straightforward and very useful when you want to see the highest values first.

Example

To sort the products table by price in descending order, you would use the following query:

SELECT id, name, price
FROM products
ORDER BY price DESC;

This would return:

id	name	price
1	Laptop	1200
4	Monitor	300
5	Headphone	150
2	Keyboard	75
3	Mouse	25

Now, the products are sorted from the highest price to the lowest price.

Sorting by Multiple Columns

You can sort by multiple columns by specifying them in the ORDER BY clause, separated by commas. The order in which you specify the columns matters. PostgreSQL will first sort by the first column, then by the second column within each group of the first column, and so on. It’s like sorting a spreadsheet: you can sort by primary column, then sort by secondary column within the primary column. This is a powerful technique for creating very specific orderings of your data.

Example

Let’s say we want to sort the products table first by price in descending order, and then by name in ascending order within each price group. This means the most expensive items will be at the top, and if there are multiple items with the same price, they will be sorted alphabetically.

SELECT id, name, price
FROM products
ORDER BY price DESC, name ASC;

To illustrate this better, let’s add some more data to our products table:

id	name	price
1	Laptop	1200
2	Keyboard	75
3	Mouse	25
4	Monitor	300
5	Headphone	150
6	Gaming Laptop	1200
7	Basic Mouse	25

Now, running the query above would return:

id	name	price
1	Laptop	1200
6	Gaming Laptop	1200
4	Monitor	300
5	Headphone	150
2	Keyboard	75
7	Basic Mouse	25
3	Mouse	25

Notice that the two laptops (Laptop and Gaming Laptop) are both priced at 1200, but they are sorted alphabetically by name. Similarly, the two mice (Mouse and Basic Mouse) are also sorted alphabetically within their price group.

Using ORDER BY with WHERE Clause

You can combine the ORDER BY clause with a WHERE clause to filter the rows before sorting them. This allows you to sort only the rows that meet a specific condition. It’s a common pattern: filter your data, then sort it to get the exact subset you need in the right order. This makes your queries more efficient and your results more meaningful.

Example

Let’s say we want to sort only the products that cost more than $50, ordered by price in descending order:

SELECT id, name, price
FROM products
WHERE price > 50
ORDER BY price DESC;

Using the original products table data:

id	name	price
1	Laptop	1200
2	Keyboard	75
3	Mouse	25
4	Monitor	300
5	Headphone	150

This query would return:

id	name	price
1	Laptop	1200
4	Monitor	300
5	Headphone	150
2	Keyboard	75

Only products with a price greater than $50 are included, and they are sorted by price in descending order.

Using ORDER BY with NULL Values

When dealing with NULL values in your data, the ORDER BY clause treats them in a specific way. By default, NULL values are considered lower than any other value when sorting in ascending order, and higher than any other value when sorting in descending order. However, you can control this behavior with the NULLS FIRST and NULLS LAST options.

NULLS FIRST

The NULLS FIRST option specifies that NULL values should appear at the beginning of the sorted results, regardless of whether you are sorting in ascending or descending order. This is useful when you want to quickly identify records where a particular field is missing.

NULLS LAST

The NULLS LAST option specifies that NULL values should appear at the end of the sorted results. This is the more common and often more intuitive behavior, as it keeps the missing data out of the way when you’re focusing on the valid values.

Example

Let’s add a discount column to our products table, and some of the products will have NULL values for the discount:

id	name	price	discount
1	Laptop	1200	0.1
2	Keyboard	75	NULL
3	Mouse	25	NULL
4	Monitor	300	0.05
5	Headphone	150	0.2

To sort the products by discount in ascending order, with NULL values appearing first, you would use the following query:

SELECT id, name, price, discount
FROM products
ORDER BY discount NULLS FIRST;

This would return:

id	name	price	discount
2	Keyboard	75	NULL
3	Mouse	25	NULL
4	Monitor	300	0.05
1	Laptop	1200	0.1
5	Headphone	150	0.2

To sort the products by discount in descending order, with NULL values appearing last, you would use the following query:

SELECT id, name, price, discount
FROM products
ORDER BY discount DESC NULLS LAST;

This would return:

id	name	price	discount
5	Headphone	150	0.2
1	Laptop	1200	0.1
4	Monitor	300	0.05
2	Keyboard	75	NULL
3	Mouse	25	NULL

Conclusion

Alright, guys! That’s pretty much everything you need to know about using ORDER BY with ASC and DESC in PostgreSQL. You’ve learned how to sort by single and multiple columns, how to combine ORDER BY with WHERE clauses, and how to handle NULL values. Mastering these techniques will allow you to retrieve and present your data in a clear, organized, and meaningful way. Keep practicing, and you’ll become a PostgreSQL sorting pro in no time! Happy querying!