Workarounds for Ordering Results Based on the IN List in PostgreSQL

The ORDER BY clause sorts the results after they are retrieved, and it doesn't have access to the order they were specified in the IN list.

However, there are some workarounds to achieve a similar effect in PostgreSQL:

1. CASE statement: You can use a CASE statement to assign a sorting order based on the value itself. This involves writing multiple conditions to check if the value in your field matches each value in the IN list and assigning a corresponding order number.

2. UNNEST with ORDINALITY (PostgreSQL 9.4+): This approach utilizes the UNNEST function with the ORDINALITY clause (available in PostgreSQL versions 9.4 and later). UNNEST treats a list of values like a table with one row per value. ORDINALITY assigns a number to each row based on its position in the original list. You can then join this temporary table with your main table and sort by the assigned order number.

3. Temporary Table: Create a temporary table containing the desired order along with the values you want to filter by. Then join this temporary table with your main table and sort by the order from the temporary table.

These methods achieve the goal of ordering results based on a predefined list, but they are more complex than a simple ORDER BY on the IN list itself.

CASE statement:

-- Sample table
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Select products with category in ('Electronics', 'Clothing') ordered by the order in the list
SELECT *
FROM products
WHERE category IN ('Electronics', 'Clothing')
ORDER BY CASE category
         WHEN 'Electronics' THEN 1
         WHEN 'Clothing' THEN 2
       END;

UNNEST with ORDINALITY (PostgreSQL 9.4+):

-- Sample table (same as above)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as above)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order list
SELECT unnest(ARRAY['Electronics', 'Clothing']) AS category_order;

-- Select products with category matching the order list
WITH ordered_categories AS (
  SELECT unnest(ARRAY['Electronics', 'Clothing']) AS category_order,
         ROW_NUMBER() OVER (ORDER BY category_order) AS order_id
)
SELECT p.*
FROM products p
JOIN ordered_categories oc ON p.category = oc.category_order
ORDER BY oc.order_id;

Temporary Table:

-- Sample table (same as above)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as above)
INSERT INTO PRODUCTS (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Temporary table with desired order
CREATE TEMP TABLE category_order (
  order_id INT PRIMARY KEY,
  category VARCHAR(50)
);

INSERT INTO category_order (order_id, category) VALUES
  (1, 'Electronics'),
  (2, 'Clothing');

-- Select products with category matching the order
SELECT p.*
FROM products p
JOIN category_order co ON p.category = co.category
ORDER BY co.order_id;

-- Drop temporary table after use
DROP TABLE category_order;

GENERATE_SERIES with CASE (all PostgreSQL versions):

This approach utilizes the GENERATE_SERIES function to create a sequence of numbers and a CASE statement to assign sorting order based on the value in your field.

-- Sample table (same as previous examples)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as previous examples)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order list
SELECT value AS category_order
FROM GENERATE_SERIES(1, ARRAY_LENGTH(ARRAY['Electronics', 'Clothing'])) AS gs(value);

-- Select products with category matching the order list
SELECT p.*
FROM products p
JOIN (
  SELECT value AS category_order,
         CASE WHEN category = 'Electronics' THEN 1
              WHEN category = 'Clothing' THEN 2
              ELSE 3 -- Add a default order for unmatched categories (optional)
         END AS order_id
  FROM GENERATE_SERIES(1, ARRAY_LENGTH(ARRAY['Electronics', 'Clothing'])) AS gs(value)
) AS oc ON p.category = oc.category_order
ORDER BY oc.order_id;

CTE with ROW_NUMBER (all PostgreSQL versions):

This method uses a Common Table Expression (CTE) with the ROW_NUMBER function to assign a position to each value based on the desired order.

-- Sample table (same as previous examples)
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(50)
);

-- Sample data (same as previous examples)
INSERT INTO products (id, name, category) VALUES
  (1, 'Shirt', 'Clothing'),
  (2, 'Laptop', 'Electronics'),
  (3, 'Headphones', 'Electronics'),
  (4, 'Jeans', 'Clothing');

-- Desired order
WITH category_order AS (
  SELECT category,
         ROW_NUMBER() OVER (ORDER BY CASE category
                                       WHEN 'Electronics' THEN 1
                                       WHEN 'Clothing' THEN 2
                                       END) AS order_id
  FROM (SELECT DISTINCT category FROM products) AS all_categories
)
-- Select products with category matching the order
SELECT p.*
FROM products p
JOIN category_order co ON p.category = co.category
ORDER BY co.order_id;