Ordering by the order of values in a SQL IN clause

Question

I am wondering if there is away  possibly a better way  to order by the order of the values in an IN   clause   The problem is that I have 2 queries  one that gets all of the IDs and the second that retrieves all the information  The first creates the order of the IDs which I want the second to order by  The IDs are put in an IN   clause in the correct order   So it d be something like  extremely simplified    SELECT id FROM table1 WHERE     ORDER BY display order  name  SELECT name  description      WHERE id IN   id s from first     The issue is that the second query does not return the results in the same order that the IDs are put into the IN   clause   One solution I have found is to put all of the IDs into a temp table with an auto incrementing field which is then joined into the second query   Is there a better option   Note  As the first query is run  by the user  and the second is run in a background process  there is no way to combine the 2 into 1 query using sub queries   I am using MySQL  but I m thinking it might be useful to have it noted what options there are for other DBs as well

User · Answer

Two solutions that spring to mind    order by case id when 123 then 1 when 456 then 2 else null end asc order by instr      id        123 456    asc    instr   is from Oracle  maybe you have locate   or charindex   or something like that

User · Answer

I just tried to do this is MS SQL Server where we do not have FIELD     SELECT table1 id      INNER JOIN      VALUES  10 1   3 2   4 3   5 4   7 5   8 6   9 7   2 8   6 9   5 10        AS X id sortorder          ON X id   table1 id     ORDER BY X sortorder   Note that I am allowing duplication too

User · Answer

The IN clause describes a set of values  and sets do not have order   Your solution with a join and then ordering on the display order column is the most nearly correct solution  anything else is probably a DBMS-specific hack  or is doing some stuff with the OLAP functions in standard SQL    Certainly  the join is the most nearly portable solution  though generating the data with the display order values may be problematic    Note that you may need to select the ordering columns  that used to be a requirement in standard SQL  though I believe it was relaxed as a rule a while ago  maybe as long ago as SQL-92

User · Answer

I think you should manage to store your data in a way that you will simply do a join and it will be perfect  so no hacks and complicated things going on   I have for instance a  Recently played  list of track ids  on SQLite i simply do   SELECT   FROM recently NATURAL JOIN tracks

User · Answer

See following how to get sorted data   SELECT       FROM      WHERE zip IN  91709 92886 92807     91356     AND user status 1 ORDER      BY provider package id DESC         FIELD zip 91709 92886 92807     91356  LIMIT 10

User · Answer

Ans to get sorted data   SELECT     FROM     ORDER  BY FIELD user id 5 3 2     50   LIMIT 10

User · Answer

I just tried to do this is MS SQL Server where we do not have FIELD     SELECT table1 id      INNER JOIN      VALUES  10 1   3 2   4 3   5 4   7 5   8 6   9 7   2 8   6 9   5 10        AS X id sortorder          ON X id   table1 id     ORDER BY X sortorder   Note that I am allowing duplication too

User · Answer

My first thought was to write a single query  but you said that was not possible because one is run by the user and the other is run in the background   How are you storing the list of ids to pass from the user to the background process   Why not put them in a temporary table with a column to signify the order   So how about this    The user interface bit runs and inserts values into a new table you create   It would insert the id  position and some sort of job number identifier  The job number is passed to the background process  instead of all the ids  The background process does a select from the table in step 1 and you join in to get the other information that you require   It uses the job number in the WHERE clause and orders by the position column  The background process  when finished  deletes from the table based on the job identifier

User · Answer

Use MySQL s FIELD   function   SELECT name  description      FROM     WHERE id IN  ids  any order   ORDER BY FIELD id   ids in order     FIELD   will return the index of the first parameter that is equal to the first parameter  other than the first parameter itself    FIELD  a    a    b    c    will return 1  FIELD  a    c    b    a    will return 3  This will do exactly what you want if you paste the ids into the IN   clause and the FIELD   function in the same order

User · Answer

SELECT ORDER NO  DELIVERY ADDRESS  from IFSAPP PURCHASE ORDER TAB  where ORDER NO in   52000077   52000079   52000167   52000297   52000204   52000409   52000126    ORDER BY instr  52000077 52000079 52000167 52000297 52000204 52000409 52000126  ORDER NO    worked really great

User · Answer

For Oracle  John s solution using instr   function works  Here s slightly different solution that worked -  SELECT id  FROM table1  WHERE id IN  1  20  45  60   ORDER BY instr  1  20  45  60   id

User · Answer

The IN clause describes a set of values  and sets do not have order   Your solution with a join and then ordering on the display order column is the most nearly correct solution  anything else is probably a DBMS-specific hack  or is doing some stuff with the OLAP functions in standard SQL    Certainly  the join is the most nearly portable solution  though generating the data with the display order values may be problematic    Note that you may need to select the ordering columns  that used to be a requirement in standard SQL  though I believe it was relaxed as a rule a while ago  maybe as long ago as SQL-92

User · Answer

Ans to get sorted data   SELECT     FROM     ORDER  BY FIELD user id 5 3 2     50   LIMIT 10

User · Answer

My first thought was to write a single query  but you said that was not possible because one is run by the user and the other is run in the background   How are you storing the list of ids to pass from the user to the background process   Why not put them in a temporary table with a column to signify the order   So how about this    The user interface bit runs and inserts values into a new table you create   It would insert the id  position and some sort of job number identifier  The job number is passed to the background process  instead of all the ids  The background process does a select from the table in step 1 and you join in to get the other information that you require   It uses the job number in the WHERE clause and orders by the position column  The background process  when finished  deletes from the table based on the job identifier

User · Answer

Give this a shot   SELECT name  description      WHERE id IN      SELECT id FROM table1 WHERE     ORDER BY      SELECT display order FROM table1 WHERE           SELECT name FROM table1 WHERE       The WHEREs will probably take a little tweaking to get the correlated subqueries working properly  but the basic principle should be sound

User · Answer

Use MySQL s FIELD   function   SELECT name  description      FROM     WHERE id IN  ids  any order   ORDER BY FIELD id   ids in order     FIELD   will return the index of the first parameter that is equal to the first parameter  other than the first parameter itself    FIELD  a    a    b    c    will return 1  FIELD  a    c    b    a    will return 3  This will do exactly what you want if you paste the ids into the IN   clause and the FIELD   function in the same order

User · Answer

If you want to do arbitrary sorting on a query using values inputted by the query in MS SQL Server 2008   it can be done by creating a table on the fly and doing a join like so  using nomenclature from OP    SELECT table1 name  table1 description      FROM  VALUES  id1 1    id2 2    id3 3       AS orderTbl orderKey  orderIdx   LEFT JOIN table1 ON orderTbl orderKey table1 id ORDER BY orderTbl orderIdx   If you replace the VALUES statement with something else that does the same thing  but in ANSI SQL  then this should work on any SQL database   Note   The second column in the created table  orderTbl orderIdx  is necessary when querying record sets larger than 100 or so  I originally didn t have an orderIdx column  but found that with result sets larger than 100 I had to explicitly sort by that column  in SQL Server Express 2014 anyways

User · Answer

Use MySQL s FIELD   function   SELECT name  description      FROM     WHERE id IN  ids  any order   ORDER BY FIELD id   ids in order     FIELD   will return the index of the first parameter that is equal to the first parameter  other than the first parameter itself    FIELD  a    a    b    c    will return 1  FIELD  a    c    b    a    will return 3  This will do exactly what you want if you paste the ids into the IN   clause and the FIELD   function in the same order

User · Answer

Two solutions that spring to mind    order by case id when 123 then 1 when 456 then 2 else null end asc order by instr      id        123 456    asc    instr   is from Oracle  maybe you have locate   or charindex   or something like that

User · Answer

Give this a shot   SELECT name  description      WHERE id IN      SELECT id FROM table1 WHERE     ORDER BY      SELECT display order FROM table1 WHERE           SELECT name FROM table1 WHERE       The WHEREs will probably take a little tweaking to get the correlated subqueries working properly  but the basic principle should be sound

User · Answer

Use MySQL s FIELD   function   SELECT name  description      FROM     WHERE id IN  ids  any order   ORDER BY FIELD id   ids in order     FIELD   will return the index of the first parameter that is equal to the first parameter  other than the first parameter itself    FIELD  a    a    b    c    will return 1  FIELD  a    c    b    a    will return 3  This will do exactly what you want if you paste the ids into the IN   clause and the FIELD   function in the same order

User · Answer

The IN clause describes a set of values  and sets do not have order   Your solution with a join and then ordering on the display order column is the most nearly correct solution  anything else is probably a DBMS-specific hack  or is doing some stuff with the OLAP functions in standard SQL    Certainly  the join is the most nearly portable solution  though generating the data with the display order values may be problematic    Note that you may need to select the ordering columns  that used to be a requirement in standard SQL  though I believe it was relaxed as a rule a while ago  maybe as long ago as SQL-92

User · Answer

Two solutions that spring to mind    order by case id when 123 then 1 when 456 then 2 else null end asc order by instr      id        123 456    asc    instr   is from Oracle  maybe you have locate   or charindex   or something like that

User · Answer

Two solutions that spring to mind    order by case id when 123 then 1 when 456 then 2 else null end asc order by instr      id        123 456    asc    instr   is from Oracle  maybe you have locate   or charindex   or something like that

User · Answer

For Oracle  John s solution using instr   function works  Here s slightly different solution that worked -  SELECT id  FROM table1  WHERE id IN  1  20  45  60   ORDER BY instr  1  20  45  60   id

User · Answer

Give this a shot   SELECT name  description      WHERE id IN      SELECT id FROM table1 WHERE     ORDER BY      SELECT display order FROM table1 WHERE           SELECT name FROM table1 WHERE       The WHEREs will probably take a little tweaking to get the correlated subqueries working properly  but the basic principle should be sound

User · Answer

I think you should manage to store your data in a way that you will simply do a join and it will be perfect  so no hacks and complicated things going on   I have for instance a  Recently played  list of track ids  on SQLite i simply do   SELECT   FROM recently NATURAL JOIN tracks

User · Answer

If you want to do arbitrary sorting on a query using values inputted by the query in MS SQL Server 2008   it can be done by creating a table on the fly and doing a join like so  using nomenclature from OP    SELECT table1 name  table1 description      FROM  VALUES  id1 1    id2 2    id3 3       AS orderTbl orderKey  orderIdx   LEFT JOIN table1 ON orderTbl orderKey table1 id ORDER BY orderTbl orderIdx   If you replace the VALUES statement with something else that does the same thing  but in ANSI SQL  then this should work on any SQL database   Note   The second column in the created table  orderTbl orderIdx  is necessary when querying record sets larger than 100 or so  I originally didn t have an orderIdx column  but found that with result sets larger than 100 I had to explicitly sort by that column  in SQL Server Express 2014 anyways

User · Answer

See following how to get sorted data   SELECT       FROM      WHERE zip IN  91709 92886 92807     91356     AND user status 1 ORDER      BY provider package id DESC         FIELD zip 91709 92886 92807     91356  LIMIT 10

User · Answer

Use MySQL FIND IN SET function     SELECT        FROM table name     WHERE id IN                ORDER BY FIND IN SET  coloumn name

User · Answer

Give this a shot   SELECT name  description      WHERE id IN      SELECT id FROM table1 WHERE     ORDER BY      SELECT display order FROM table1 WHERE           SELECT name FROM table1 WHERE       The WHEREs will probably take a little tweaking to get the correlated subqueries working properly  but the basic principle should be sound

User · Answer

SELECT ORDER NO  DELIVERY ADDRESS  from IFSAPP PURCHASE ORDER TAB  where ORDER NO in   52000077   52000079   52000167   52000297   52000204   52000409   52000126    ORDER BY instr  52000077 52000079 52000167 52000297 52000204 52000409 52000126  ORDER NO    worked really great

User · Answer

My first thought was to write a single query  but you said that was not possible because one is run by the user and the other is run in the background   How are you storing the list of ids to pass from the user to the background process   Why not put them in a temporary table with a column to signify the order   So how about this    The user interface bit runs and inserts values into a new table you create   It would insert the id  position and some sort of job number identifier  The job number is passed to the background process  instead of all the ids  The background process does a select from the table in step 1 and you join in to get the other information that you require   It uses the job number in the WHERE clause and orders by the position column  The background process  when finished  deletes from the table based on the job identifier

User · Answer

My first thought was to write a single query  but you said that was not possible because one is run by the user and the other is run in the background   How are you storing the list of ids to pass from the user to the background process   Why not put them in a temporary table with a column to signify the order   So how about this    The user interface bit runs and inserts values into a new table you create   It would insert the id  position and some sort of job number identifier  The job number is passed to the background process  instead of all the ids  The background process does a select from the table in step 1 and you join in to get the other information that you require   It uses the job number in the WHERE clause and orders by the position column  The background process  when finished  deletes from the table based on the job identifier

User · Answer

The IN clause describes a set of values  and sets do not have order   Your solution with a join and then ordering on the display order column is the most nearly correct solution  anything else is probably a DBMS-specific hack  or is doing some stuff with the OLAP functions in standard SQL    Certainly  the join is the most nearly portable solution  though generating the data with the display order values may be problematic    Note that you may need to select the ordering columns  that used to be a requirement in standard SQL  though I believe it was relaxed as a rule a while ago  maybe as long ago as SQL-92

User · Answer

Use MySQL FIND IN SET function     SELECT        FROM table name     WHERE id IN                ORDER BY FIND IN SET  coloumn name

[mysql] Ordering by the order of values in a SQL IN() clause

Examples related to mysql

Examples related to sql

Examples related to sql-order-by