Solved

mysql ORDER BY based on IN clause

Posted on 2011-02-18
5
254 Views
Last Modified: 2012-05-11
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
Comment
Question by:pmsguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34926691
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
 

Author Comment

by:pmsguy
ID: 34926732
Is there an easier way to do this query?
The IN list will be dynamic.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34926764
not that I know of.
but if the "IN" list will be dynamic, so can be your order by?
0
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 34927178
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34927230
actually, that is a good one!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question