CODEWITHSUNDEEP
mysql
Robert Moritz
Robert Moritz

Reputation: 85

MySQL - IN (array) ORDER BY original

I'm trying to do a search like this:

SELECT * FROM questions WHERE id IN (4, 5, 1)

but, the result is ordered by ID

+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
| id | question                                                                                   | answer                     | notes | difficulty | toggle |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
|  1 | VAR is much talked about in football - what does VAR stand for?                            | Video Assistant Referee    |       | Medium     |      1 |
|  4 | Which large construction company has gone into receivership with debts of £1.5 billion?   | Carillion                  |       | Easy       |      0 |
|  5 | What does PFI stand for in PFI contracts?                                                  | Private Finance Initiative |       | Easy       |      1 |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+

however, i need the results in the order of the array. I've found somewhere the following:

SELECT * FROM questions WHERE id IN (4, 5, 1) ORDER BY FIND_IN_SET(id, '4, 5, 1');

which kinda works, but returns it in reverse:

+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
| id | question                                                                                   | answer                     | notes | difficulty | toggle |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
|  1 | VAR is much talked about in football - what does VAR stand for?                            | Video Assistant Referee    |       | Medium     |      1 |
|  5 | What does PFI stand for in PFI contracts?                                                  | Private Finance Initiative |       | Easy       |      1 |
|  4 | Which large construction company has gone into receivership with debts of £1.5 billion?   | Carillion                  |       | Easy       |      0 |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+

Then, the proposed solution was to add DESC at the end

SELECT * FROM questions WHERE id IN (4, 5, 1) ORDER BY FIND_IN_SET(id, '4, 5, 1') desc;

which return this, which i don't get at all

+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
| id | question                                                                                   | answer                     | notes | difficulty | toggle |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+
|  4 | Which large construction company has gone into receivership with debts of £1.5 billion?   | Carillion                  |       | Easy       |      0 |
|  1 | VAR is much talked about in football - what does VAR stand for?                            | Video Assistant Referee    |       | Medium     |      1 |
|  5 | What does PFI stand for in PFI contracts?                                                  | Private Finance Initiative |       | Easy       |      1 |
+----+--------------------------------------------------------------------------------------------+----------------------------+-------+------------+--------+

Why is 4, 1, 5 and not 4, 5, 1? How do I make it return 4, 5, 1?

Cheers

Upvotes: 0

Views: 27

Answers (2)

Thomas G
Thomas G

Reputation: 10216

You should use an ORDER BY FIELD() or an ORDER BY CASE WHEN...

In your case the first one is probably the most simpler

SELECT * 
FROM questions 
WHERE id IN (4, 5, 1) 
ORDER BY FIELD(id, 4, 5, 1)

SQLfiddle

Upvotes: 2

P.Salmon
P.Salmon

Reputation: 17615

drop table if exists t;

create table t(id int, question varchar(100));

insert into t values                 
(  1 , 'VAR is much talked about in football - what does VAR stand for?'                            ),
(  4 , 'Which large construction company has gone into receivership with debts of £1.5 billion?'   ),
(  5 , 'What does PFI stand for in PFI contracts?'   ); 

select * 
from t
order by case when id = 4 then 1
                  when id = 1 then 2
                  else 3
            end;

+------+------------------------------------------------------------------------------------------+
| id   | question                                                                                 |
+------+------------------------------------------------------------------------------------------+
|    4 | Which large construction company has gone into receivership with debts of £1.5 billion? |
|    1 | VAR is much talked about in football - what does VAR stand for?                          |
|    5 | What does PFI stand for in PFI contracts?                                                |
+------+------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Upvotes: 0

Related Questions