Sort the rows according to the order specified in WHERE IN clause

Sorting: beyond MySQL and FIELD() function

Not all SQL databases natively support the ORDER BY FIELD() syntax. Nonetheless, other techniques such as CASE statements, VALUES table, or temporary structures can offer similar capabilities across different SQL dialects.

Array positions: PostgreSQL's custom order magic

In PostgreSQL, array positions can do a splendid mimicry of ORDER BY FIELD():

SELECT * FROM your_table
WHERE your_column = ANY ('{value1, value2, value3}'::text[])
ORDER BY
  idx(array['value1','value2','value3'], your_column);


The idx() function handle, as outlined in the official PostgreSQL documentation, returns the index of the first occurrence of a value in an array, thereby sorting them based on this custom value list.

CASE statements: your SQL sorting Swiss army knife

CASE statements offer a solution for flexible custom sorting, applicable to SQL Server, SQLite, and several other databases:

SELECT * FROM your_table
ORDER BY
  CASE your_column
    WHEN 'value1' THEN 1
    WHEN 'value2' THEN 2
    WHEN 'value3' THEN 3
    ...
    ELSE n
  END;


This bad boy assigns an explicit order to each value, making ORDER BY FIELD() green with envy.

Permanent table: the knight in shining armor for consistent sort order

If you're eyeing a long-term solution, think about creating a definitive table to house your sort order:

CREATE TABLE sort_order (
  sort_id INT PRIMARY KEY,
  your_column_value VARCHAR(255)
);

INSERT INTO sort_order (sort_id, your_column_value)
VALUES (1, 'value1'), (2, 'value2'), (3, 'value3');
-- insert epic sorting matrix

SELECT * FROM your_table
JOIN sort_order
  ON your_table.your_column = sort_order.your_column_value
ORDER BY sort_order.sort_id;
-- apply epic sorting


Your data will now be sorted out in the consistent order — it's living life on Easy Street!

Exploring versatile sorting methods

FIND_IN_SET: Your unruly list's straightjacket

The FIND_IN_SET function, your go-to for comma-separated lists in MySQL, is superbly handy for sorting:

SELECT * FROM your_table
WHERE FIND_IN_SET(your_column, 'value1,value2,value3') > 0
ORDER BY FIND_IN_SET(your_column, 'value1,value2,value3');


This retrieves rows with the specified values and organises them according to their position within the provided list. Poof! Your list is now a well-behaved schoolboy.

INNER JOIN: Sorting's dynamic duo

For complicated sorting involving multiple fields or conditions, an INNER JOIN on a temp table or subquery can be a surprisingly powerful weapon:

SELECT your_table.*
FROM your_table
INNER JOIN (
  SELECT 'value1' as sort_value, 1 as sort_order
  UNION ALL
  SELECT 'value2', 2
  UNION ALL
  SELECT 'value3', 3
  ...
) as sort_order_table
ON your_table.your_column = sort_order_table.sort_value
ORDER BY sort_order_table.sort_order;
-- select and sort, the dynamic duo!


You join your original table with this makeshift table-like structure that holds your desired sort order. We're talking luxury-suite-level vibes here.

Incorporate exceptional cases in your sorting saga

You might encounter scenarios where you need to account for null values or additional criteria. Good news! You can still have your cake and eat it:

SELECT * FROM your_table
ORDER BY
  CASE
    WHEN your_column IS NULL THEN 1
    ELSE 0
  END,
  FIELD(your_column, 'value1', 'value2', 'value3', ...);


This ensures all null values are sorted appropriately, while the rest of the rows follow your custom order. High five!