Can I sort by the value of an IN query?
The following defaults to "order item_id lowest first" but i actually want the sort as entered... is this possible?
e.g.
select item_id, item_title, item_source from items where item_id IN ('1676','1559','1672')
I want to return:
item_id item_title item_source
------- ---------- -----------
1676 item_a source_a
1559 item_f source_f
1672 item_c source_c
You can use the WHERE clause with or without the ORDER BY statement. You can filter records by finite values, comparison values or with sub-SELECT statements.
To sort by a column: Type: SELECT columns FROM table ORDER BY sort_column [ASC | DESC]; columns is one or more comma-separated column names, sort_column is the name of the column on which to sort the result, and table is the name of the table that contains columns and sort_column.
ORDER BY LIMIT is used to get rows from table in sorting order either in ascending or descending order and to limit rows in result-set. ORDER BY LIMIT is not supported in all databases. ORDER BY LIMIT works only in MySQL.
The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
You can JOIN
these values in the IN
predicate like so:
SELECT
i.item_id,
i.item_title
i.item_source
FROM items i
INNER JOIN
(
SELECT 0 sortid, 1676 id
UNION ALL
SELECT 1, 1559
UNION ALL
SELECT 2, 1672
) t ON i.item_id = t.id
ORDER BY t.sortid
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With