Ordering by the order of values in a SQL IN clause

0 votes

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.

Feb 4 in Database by Vaani
• 7,020 points
1,543 views

1 answer to this question.

0 votes

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.

answered Feb 4 by Neha
• 8,940 points

Related Questions In Database

0 votes
0 answers

SQL to find the number of distinct values in a column

In a column, I can choose each ...READ MORE

Aug 15 in Database by Kithuzzz
• 20,660 points
84 views
0 votes
0 answers

What is the purpose of Order By 1 in SQL select statement?

I discovered numerous views with an order ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
66 views
0 votes
0 answers

How can I select the first day of a month in SQL?

I only need to choose the given ...READ MORE

Aug 14 in Database by Kithuzzz
• 20,660 points
59 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
77 views
0 votes
1 answer

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

We can use expressions as well in ...READ MORE

answered May 31 in Others by Sohail
• 2,960 points
67 views
0 votes
1 answer

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

Use MySQL FIND_IN_SET function: SELECT * ...READ MORE

answered Jun 6 in Others by nisha
• 2,190 points
70 views
0 votes
1 answer
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24, 2019 in Big Data Hadoop by Gitika
• 65,890 points
544 views
0 votes
1 answer

What is the definition of cardinality in SQL?

Cardinality is defined as the "number of ...READ MORE

answered Feb 15 in Database by Neha
• 8,940 points
508 views
0 votes
1 answer

How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE

answered Feb 9 in Database by Neha
• 8,940 points
146 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP