Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

mysql ORDER BY based on IN clause

I want to ORDER my sql query based on the IN clause

select * from acct where acct_num in (200,100,150) order by (200,100,150)

My result is ordered by
100,150,200
I assume because acct_num is an index.

I want my resulting order to be
200,100,150
0
pmsguy
Asked:
pmsguy
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you then need to write the order by like that:
select * from acct where acct_num in (200,100,150)
 order by case when acct_num = 200 then 0 when acct_num =  100 then 2 else 3 end

Open in new window

0
 
pmsguyAuthor Commented:
Is there an easier way to do this query?
The IN list will be dynamic.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not that I know of.
but if the "IN" list will be dynamic, so can be your order by?
0
 
Ovid BurkeCreative DirectorCommented:
Try this:
SELECT * FROM acct WHERE acct_num IN (200,100,150) ORDER BY FIELD(acct_num,200,100,150);

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, that is a good one!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now