PostgreSQL Order By with Examples

In this PostgreSQL tutorial, we will study the use of the Postgresql order by clause and we are going to discuss the following list of topics.

• Postgresql order by
• Postgresql order by limit
• Postgresql order by json field
• Postgresql order by specific order
• Postgresql order by date
• Postgresql order by not working
• Postgresql order by character varying
• Postgresql order by list of values
• Postgresql order by group by
• Postgresql order by field
• Postgresql order by array length
• Postgresql order by after union
• Postgresql order by group by
• Postgresql order by count
• Postgresql order by desc limit 1
• Postgresql order by length
• Postgresql order by enum
• Postgresql order by error
• Postgresql order by column does not exist

Postgresql order by

In Postgresql, the ORDER BY is a clause that allows us to sort rows returned by a SELECT clause in ascending or descending order based on a sort expression. When we query data from a table, the SELECT statement returns rows in an unspecified order.

To sort the rows of the result set, we use the ORDER BY clause in the SELECT statement. Let’s check the basic syntax of the ORDER BY clause.

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

Let’s check a query for an example.

SELECT name, address FROM employee ORDER BY name;

Let’s check the output of the above query.

Read PostgreSQL WHERE with examples

Postgresql order by limit

LIMIT in Postgresql permits us to retrieve only a part of the rows that are generated by the rest of the query. No more than that many rows will be returned if a limit count is given.

When using LIMIT, it is essential to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise, we can get an unpredictable subset of the query’s rows. Let’s check the query now.

SELECT * FROM "employee" WHERE "country" = 'Texas' LIMIT 1;

Let’s check the output of the above query.

Read How to connect to PostgreSQL database

Postgresql order by specific order asc desc

In Postgresql, ORDER BY specific order means how we can sort the values in ascending or descending order. For checking the column in ascending order we will use ASC and for checking in descending order we will use DESC.

We have already created a table BILLS in our previous topic and with the help of that, we will check the ascending and descending order accordingly. Let’s check the query for ASC and DESC.

SELECT billing_date FROM BILLS
 ORDER BY billing_date DESC;
SELECT due_date FROM BILLS ORDER BY due_date ASC;

Let’s check the output now.

Read PostgreSQL DROP COLUMN

Postgresql order by date

In Postgresql, to perform a query that’s sorted by date, we will use the ORDER BY clause in our SELECT statement. This clause will allow us to sort rows in ascending or descending order based on the specified criteria.

Queries in Postgresql are returned in an unspecified order by default. Hence we can control how the results are sorted by using ORDER BY. Let’ check the query now.

SELECT billing_date FROM BILLS
 ORDER BY billing_date DESC;

Let’s check the output now.

Read PostgreSQL INSERT INTO table

Postgresql order by not working

In PostgreSQL, it uses libraries from the operating system for local languages support. When ORDER BY is used clause without COLLATE clause, then Postgresql uses default collate which can be shown by command SHOW lc_collate. The index is important just for the speed-up of the ORDER BY clause. It doesn’t impact orders.

Some locales can use different ordering than we expect. If we expect a different locale then we can use COLLATE clause after ORDER BY or recreate the database with a different locale by dump/load. Let’s check the query below.

SELECT datname, datcollate FROM pg_database;

Let’s check the output now.

Read PostgreSQL ALTER TABLE + 19 Examples

Postgresql order by list of values

In Postgresql, we can check a particular list of values with the help of the ORDER BY clause. So, we have a table EMPLOYEE in which we will check for the particular ID’s suppose 1 and 5. For that, we will implement the following query.

SELECT * FROM EMPLOYEE WHERE ID IN (1,5) ORDER BY ID;

Let’s check the output of the above query.

Read PostgreSQL DATE Functions with Examples

Postgresql order by json field

JSON are data types in Postgresql that are used for storing JSON. It is abbreviated as JavaScript Object Notation. It can also be stored as text but this data type has the asset of enforcing that every stored value is valid according to the JSON rules.

In the query below we have to get the array elements as separate rows to be able to order them which is the inner query data. Then we will reconstruct the JSON object. Let’s check the query.

SELECT
    person_id,
    name,
    jsonb_build_object(
        'fields',
        jsonb_agg(element ORDER BY element->'rank' DESC)
    )
FROM (
    SELECT
        person_id,
        name,
        jsonb_array_elements(field->'fields') AS element
    FROM person
) data
WHERE person_id = 42
GROUP BY
    person_id,
    name;

Let’s check the output of the above query.

Read PostgreSQL ADD COLUMN + 17 Examples

Postgresql order by character varying

In Postgresql, character varying accepts strings of any size and is generally used without length specifier. It stores up to n characters. So we will use the name column in the table UnitedStates of VARCHAR datatype in the example. Let’s check the query now

CREATE TABLE UnitedStates (
id SERIAL PRIMARY KEY,
url VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL
);

INSERT INTO UnitedStates (id, url, name) VALUES
(101,'https://sqlserverguides.com','Postgresql'),
(102,'https://sqlserverguides.com','MongoDB'),
(103,'https://sqlserverguides.com','Sql'),
(104,'https://sqlserverguides.com','Oracle');

SELECT id, url, name FROM UnitedStates ORDER BY name;

Let’s check the output of the above query.

Read PostgreSQL vs SQL Server

Postgresql order by group by

In Postgresql, the task of the GROUP BY clause is to sort the result set by student id and adds up the fee that belongs to the same student. We have created a table students in which when the student id changes, it adds the row to the returned result set. The below statement uses the ORDER BY clause with the GROUP BY clause.

SELECT stu_id, SUM (fee) FROM students GROUP BY stu_id ORDER BY SUM (fee) DESC;

Let’s check the output of the above query.

Read How to create database in PostgreSQL

Postgresql order by array length

In Postgresql, the ARRAY_LENGTH() function is used to return the length of the desired array dimension. This function takes two parameters, an array and an integer. The array is what we want to measure and the integer describes which dimension should be measured in the array’s length.

The ORDER BY clause is used for getting the array length in a separate column. We can even sort in descending order of the array length in the below example.

select id, city, array_length(city, 1) from USA ORDER BY array_length(city, 1) desc;

Let’s check the output of the above query.

Read PostgreSQL DATE Format

Postgresql order by after union

In Postgresql, UNION is an operator which merges result sets of more than two SELECT statements into one result set. This operator places rows from the result set of the first query after, before or among the rows from the result set of the second query.

To sort rows in the final result set, we use the ORDER BY clause in the second query. Now we will learn UNION ALL with ORDER BY clause example. For sorting the result returned by the UNION operator, we will place the ORDER BY clause in the below query.

SELECT * FROM top_books UNION ALL SELECT * FROM popular_books ORDER BY title;

Let’s check the output of the above query.

Read PostgreSQL WHERE IN with examples

Postgresql order by count

In Postgresql, COUNT is a function that gives us the number of rows that matches a particular condition of a query. We’ll group the results by country and count the number of employee IDs from each country. We can also use COUNT to get the aggregate values.

Also, we will sort the groups in descending order by the number of employees. That way, the countries with the greatest number of users will appear at the top. Let’s check the query

SELECT address, COUNT(id) FROM employee GROUP BY address ORDER BY COUNT(id) DESC ;

Let’s check the output of the above query.

Read PostgreSQL CASE with Examples

Postgresql order by length

In Postgresql, the length function will accept a string as a parameter. A string can be any of the following data types which are char, varchar or text. The length function basically returns the number of characters in a string. Now we will be using the order by clause with length function in the below query.

SELECT address,LENGTH(address)len FROM employee ORDER BY LENGTH(address)DESC;

Let’s check the output of the above query.

Read Postgresql change column data type

Postgresql order by desc limit 1

In PostgreSQL, the SELECT statement is used to fetch records from more than single table and limit the number of records returned fixed on a limit value. The ORDER BY claused is optional but it is used in the SELECT LIMIT statement so that we can order the results and target those records that we want to return.

We have already created a table employee and let’s see how to use a SELECT statement with a LIMIT clause in PostgreSQL.

SELECT * FROM "employee" WHERE "id" = '1' ORDER BY "id" DESC LIMIT 1;

Let’s check the output of the above query.

Read PostgreSQL TO_CHAR function

Postgresql order by column does not exist

In Postgresql, the column does not exist means an exception will happen when a column does not exist in our table. If the searching column does not exist in the table then it will raise the exception that the column does not exist in the table.

The following example shows that if a searching column does not exist in our table it will throw an exception that the column name does not exist.

\d+ emp;
SELECT address, name FROM emp ORDER BY address;

Let’s check the output of the above query.

Read How to import CSV file into PostgreSQL

Postgresql order by enum

In Postgresql, an enum is a customizable datatype comprising a static, ordered set of values. They are equal to the enum types held up in various programming languages.

A famous example of an enum type is days of the week, or a set of status values for a piece of data. Let’s check it’s query with ORDER BY clause.

\d parcel;
SELECT parcel_id, parcel_type FROM parcel
ORDER BY parcel_id;

Let’s check the output of the above query.

Read How to backup PostgreSQL database

Postgresql order by error

In Postgresql, an order by clause will through an error only if the give column name after the ORDER BY clause is not present in the table created. In the query below the city is not present in the table. Let’s understand with the help of implementation.

SELECT firstname, lastname from students ORDER BY city;

Let’s check the output of the above code.

You may like the following PostgreSQL tutorials:

• Postgresql Rank function
• Postgresql Concat
• Postgresql length of string
• Postgresql Format + 28 Examples
• PostgreSQL Delete Row

In this tutorial, we learned the use of the Postgresql order by clause discussed the following list of topics.

• Postgresql order by
• Postgresql order by limit
• Postgresql order by json field
• Postgresql order by specific order
• Postgresql order by date
• Postgresql order by not working
• Postgresql order by character varying
• Postgresql order by list of values
• Postgresql order by group by
• Postgresql order by field
• Postgresql order by array length
• Postgresql order by after union
• Postgresql order by group by
• Postgresql order by count
• Postgresql order by desc limit 1
• Postgresql order by length
• Postgresql order by enum
• Postgresql order by error
• Postgresql order by column does not exist