0

I am trying to display the table using where condition on id column, but when I run the query the query, the table displays without where condition 

Table: test

id  name
1   aaa
2   bbb
3   ccc

I want to display the table like below :

Select * from test where id in ('2','3','1')

when I run the above query it is displayed as failing where condition.

1. aaa
2. bbb
3. ccc
Maksym Fedorov
  • 6,383
  • 2
  • 11
  • 31
preethi
  • 885
  • 5
  • 20
  • 39
  • How is it failing? - were you expecting output to be in the order of the in clause? – P.Salmon Feb 12 '19 at 10:56
  • yes , I want it to be displayed in order of the in clause. – preethi Feb 12 '19 at 10:56
  • Yes @P.Salmon 's duplicate works assuming you don't want to query `in (2,2,3,1)` and get two id 2 records.. Your expected results does not show this so i don't know if this is a feature. – Raymond Nijland Feb 12 '19 at 10:59
  • Thanks Maxim, ORDER BY IN solved my issue partially . I have 100 column id's I want to display 55, 21, 36, 47 first and then all the id's respectively , Do I have write all the id's in the ORDER BY In clause. – preethi Feb 12 '19 at 11:08

2 Answers2

0

try below query

select * from test 
  where id in (2,3,1) 
  order by 
    case id
      when 2 then 1
      when 3 then 2
      when 1 then 3
    end

here is the link for db-fiddle

marmeladze
  • 6,468
  • 3
  • 24
  • 45
0

Try this

SELECT 
    *
FROM
    test
WHERE
    id in ('2','3','1')
ORDER BY FIELD(id,'2','3','1');
jithin giri
  • 733
  • 2
  • 6
  • 17