PostgreSQL order by list of values

As a database developer, I’ve recently encountered a situation where standard sorting just doesn’t fulfil my requrement. I need rows returned in a particular order that doesn’t follow typical ascending or descending patterns. In this tutorial, I’ll show you how to use custom sorting in PostgreSQL using the ORDER BY clause with a list of values.

Postgresql order by list of values

Let’s explore several examples of how to achieve custom sorting in PostgreSQL.

Example-1: Using the basic approach

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;

After executing the above query, I got the expected output, as shown in the screenshot below.

Example 2: Using CASE Expressions with Order By

The CASE expression provides a flexible way to implement custom sorting logic. Here, it assigns numeric values to each product name and sorts based on those values. This is particularly useful when you have a small set of predefined values that need custom ordering.

SELECT * FROM Sales
ORDER BY 
    CASE 
        WHEN product_name = 'Car' THEN 1
        WHEN product_name = 'Mobile' THEN 2
        WHEN product_name = 'TV' THEN 3
        ELSE 4
    END;

After executing the above query, I got the expected output as shown in the screenshot below.

Example 3: Using a Continuous Sort Function

You can use a datatype with a continuous sort function, like strings or floats. In the below query, I’m using string values (‘1’, ‘2’) rather than integers. This ensures that other categories will sort after our prioritized ones when we concatenate ‘3’ with their names.

SELECT * FROM Sales
ORDER BY 
    CASE product_name
        WHEN 'Car' THEN '1'
        WHEN 'Mobile' THEN '2'
        ELSE '3' || product_name  -- Ensures other categories sort after these
    END;

After executing the above query, I got the expected output, as shown in the below screenshot.

Example 4: Using a Position-Value Mapping Table

For more complex sorting needs, significantly when the ordering logic might change:

First, let us execute the below query to create a mapping table.

CREATE TABLE priority_order (
    value TEXT PRIMARY KEY,
    sort_order INTEGER NOT NULL
);

After executing the above query, the table is created successfully.

Let us insert priority values with their desired order using the query below.

INSERT INTO priority_order (value, sort_order) VALUES
    ('High', 1),
    ('Medium', 2),
    ('Low', 3);

After executing the above query, I got the expected output shown in the screenshot below.

Finally, we can use the below query using the mapping table.

SELECT p.*
FROM products p
LEFT JOIN priority_order po ON p.priority = po.value
ORDER BY po.sort_order;

Example 5: Ordering by Position in an Array

PostgreSQL allows sorting based on a value’s position in an array. The below query orders results according to where each value appears in the specified array. Values not found in the array will be sorted last.

SELECT * FROM Sales
ORDER BY array_position(ARRAY['High', 'Medium', 'Low'], product_name);

Conclusion

PostgreSQL’s ORDER BY clause provides you with great flexibility for custom sorting. Whether you’re working with a simple list of status values or complex hierarchical data structures, the above examples will help you to present the data exactly how you require it.

You may also like the articles below.

• PostgreSQL Order By JSON Field
• PostgreSQL Order By Date
• Postgresql order by limit
• PostgreSQL Order By
• PostgreSQL ADD COLUMN