Introduction to PostgreSQL ORDER BY DESC
To get the set of results, we use the SELECT statement, which returns us the rows from the table in random order. So to sort this result set, we use the PostgreSQL ORDER BY clause. When we use the ORDER BY clause with the SELECT statement for querying the data, the rows returned from the table can be sorted in ascending order or in descending order. We can use the columns as well as expressions for sorting the results in descending order. The PostgreSQL allows us to sort the rows in descending order when we specify the keyword DESC with the SELECT statement for sorting the rows of the table in the descending order.
Syntax:
Consider the following syntax statement to understand the PostgreSQL ORDER BY DESC syntax:
SELECT
column_name_1,
column_name_2
FROM
table_name
ORDER BY
column_name_1 [ DESC],
column_name_2 [ DESC];
Explanation:
- We need to define the name of the columns in the PostgreSQL clause to sort the results.
- It is possible in PostgreSQL to sort the result of the table with multiple columns as well, for which we need to specify the column names separated by commas.
- We can use the columns as well as expressions for sorting the results in descending order.
How does ORDER BY DESC work in PostgreSQL?
- The PostgreSQL allows us to sort the result returned by the SELECT statement in descending order; To do so, we need to specify the ORDER BY clause with the DESC keyword.
- If we do not define the DESC keyword, the ORDER BY clause by default sorts the result in ascending order.
- We can sort the result in descending order with multiple columns as well. We need to define the comma-separated column names with the DESC keyword in the ORDER BY clause.
- We can use the columns as well as expressions for sorting the results in descending order.
Examples of PostgreSQL ORDER BY DESC
We will create a table of name Tenants. Also, add some data into the Tenants table for understanding the ORDER BY DESC clause.
Use the following statement to create the Tenants table, which consists of three columns:
id,
firstName,
lastName
contact.
CREATE TABLE Tenants (
id serial NOT NULL PRIMARY KEY,
firstName VARCHAR,
lastName VARCHAR,
email VARCHAR,
contact VARCHAR
);
Now, we will insert some rows into the Tenants table with the help of the INSERT statement as follows:
INSERT INTO Tenants (firstName, lastName, email, contact)
VALUES
('Armstrong', 'Neil', 'Armstrong.Neil@mail.com', '111-222-333'),
('Bean', 'Roy', 'Bean.Roy@mail.com', '111-222-333'),
('Herbert', 'george', 'Herbert.george@mail.com', '111-222-333'),
('Gray', 'Thomas', 'Gray.Thomas@mail.com', '111-222-333'),
('Frost', 'Robert', 'Frost.Robert@mail.com', '111-222-333'),
('Eluard', 'Paul', 'Eluard.Paul@mail.com', '111-222-333'),
('Drake', 'Nick', 'Drake.Nick@mail.com', '111-222-333'),
('Laurel', 'Stan', 'Laurel.Stan@mail.com', '111-222-333'),
('Zola', 'Emile', 'Zola.Emile@mail.com', '111-222-333'),
('King', 'William', 'King.William@mail.com', '111-222-333'),
('Baba', 'Meher', 'Baba.Meher@mail.com', '111-222-333'),
('Arselan', 'Aip', 'Arselan.Aip@mail.com', '111-222-333');
select * from Tenants;
Now retrieve the data from the Tenants table with the help of the SELECT statement as follows:
SELECT
id,
firstName,
lastName,
email,
contact
FROM
Tenants;
Illustrate the content of the Tenants table by using the following snapshot:
Consider the following example where you will use only one column in the ORDER BY clause.
SELECT
id,
firstName,
lastName,
email,
contact
FROM
Tenants
ORDER BY
firstName DESC;
In the above SQL statement, we have specified the ORDER BY clause with the column firstName and specified the DESC keyword, specifying the descending order. Illustrate the result of the above statement by using the following snapshot:
Consider the following example where you will use multiple columns in the ORDER BY clause.
SELECT
id,
firstName,
lastName,
email,
contact
FROM
Tenants
ORDER BY
lastName DESC,
firstName DESC;
In the above SQL statement, we have specified the ORDER BY clause with the columns lastName and firstName and specified the DESC keyword, specifying the descending order.
Illustrate the result of the above statement by using the following snapshot:
Consider the example where we will use the expression in the ORDER Y clause.
We will use the LENGTH() function in the ORDER BY clause for sorting the result set as per the length of the firstName column. Consider the following SQL statement to understand the same, SELECT id, firstName, lastName, email, contact FROM Tenants ORDER BY LENGTH(firstName) DESC;
In the above SQL statement, we have specified the ORDER BY clause with LENGTH(firstName), which returns us the length of the firstName characters for each row and specifies the DESC keyword, which specifies the descending order.
Illustrate the result of the above statement by using the following snapshot:
The LENGTH() function provided by PostgreSQL takes a string as an input and returns the input string’s length.
Conclusion
We hope from the above article you have understood how to use PostgreSQL SELECT statement with the ORDER BY clause to return the result set in descending order. Also, we have added several examples to understand it in detail.
Recommended Articles
This is a guide to PostgreSQL ORDER BY DESC. Here we also discuss the definition and how does order by desc work in postgresql? Along with different examples and its code implementation. You may also have a look at the following articles to learn more –
