PostgreSQLSQLRDBMS
Last updated at 2023-09-07

ORDER BY Clause to Sort Results in PostgreSQL

ClickUp
Last Edit By
Last edited time
Sep 7, 2023 01:14 PM
Metatag
Slug
order-by-postgres-sorting-results
AI Status
FULL
Published
Published
Date
Sep 7, 2023
Category
PostgreSQL
SQL
RDBMS
By default, when you execute a SELECT query in PostgreSQL, it doesn't guarantee any specific order for the results.
If you want to display the query results in a particular order, you can use the ORDER BY clause.
This clause allows you to sort the rows in the result set based on one or more columns or expressions.
Sorting is crucial when you want to present data in a meaningful way, such as sorting a list of products by price or sorting names alphabetically.

Sample Code

To illustrate how to use the ORDER BY clause, we will first create a sample table and insert four values into it.
Then, we'll show you a complete SQL query that uses the ORDER BY clause to sort the results.
Finally, we'll discuss different sorting methods and examples of single and multiple column sorting.

Let's Create Table And Insert 4 Values

CREATE TABLE posts ( id SERIAL PRIMARY KEY, title VARCHAR(255), categories TEXT[], lastEditedAt TIMESTAMP, cover VARCHAR(255) NULL ); INSERT INTO posts (title, categories, lastEditedAt, cover) VALUES ('Post 1', ARRAY['Tech', 'Programming'], '2023-01-15 10:30:00', NULL), ('Post 2', ARRAY['Science', 'Space'], '2023-02-20 14:45:00', "science"), ('Post 3', ARRAY['Food'], '2023-03-10 08:15:00', "food"), ('Post 4', ARRAY['Tech', 'Gaming'], '2023-04-05 16:00:00', "tech");
Here, we've created a posts table and inserted four sample rows of data.

Complete Query with ORDER BY Clause

The following SQL query demonstrates how to use the ORDER BY clause to sort the query results:
SELECT id, title, LENGTH(categories) categories_count, lastEditedAt, cover FROM posts ORDER BY title ASC, categories_count DESC, cover ASC NULLS FIRST;
In this query, we select specific columns from the posts table and sort the results based on three criteria: title in ascending order, categories_count in descending order, and cover with NULLs appearing first.

Available Sort Method

The ORDER BY clause allows you to specify sorting methods for each column or expression you want to sort by. Here are the available sorting methods:

ASC (Ascending)

This method sorts the PostgreSQL result in ascending order, meaning the values go from smallest to largest when sorting by the specified column.

DESC (Descending)

This method sorts the PostgreSQL result in descending order, meaning the values go from largest to smallest when sorting by the specified column.

NULLS FIRST

This method ensures that rows with NULL values in the specified column appear at the beginning when sorting in ascending order.

NULLS LAST

This method ensures that rows with NULL values in the specified column appear at the end when sorting in ascending order.

Single Column Sorting

You can perform single column sorting using the ORDER BY clause. Here's an example:
SELECT title, LENGTH(categories) categories_count FROM posts ORDER BY title ASC;
In this query, we select only the title and categories_count columns and sort the results by title in ascending order.

Multiple Column Sorting

You can also sort by multiple columns in a specific order. Here's an example:
SELECT title, LENGTH(categories) categories_count FROM posts ORDER BY title ASC, categories_count DESC;
In this query, we sort the results first by title in ascending order and then by categories_count in descending order, creating a hierarchical sorting.

By Expression Sorting

You can sort by expressions, not just columns. For instance, in the first query shown, we calculate the categories_count using the LENGTH function and sort by it.
SELECT title, LENGTH(categories) categories_count FROM posts ORDER BY title ASC, categories_count DESC;

Wrap Up!

The ORDER BY clause in PostgreSQL is a powerful tool for sorting query results according to your requirements.
You can sort by one or more columns, use different sorting methods, and even sort by expressions.
This allows you to present your data in a structured and meaningful way, making it easier for users to interpret the results.
Happy Sorting!

Discussion (0)

Related Posts