Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I order by IN value

Tags:

sql

mysql

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
like image 576
StudioTime Avatar asked Nov 04 '12 20:11

StudioTime


People also ask

Can we use ORDER BY with WHERE clause in SQL?

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.

How do I sort a column by value in SQL?

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.

Can we use ORDER BY with limit?

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.

What is the use of ORDER BY?

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.


1 Answers

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

SQL Fiddle Demo

like image 177
Mahmoud Gamal Avatar answered Sep 29 '22 18:09

Mahmoud Gamal