?
Solved

ORDER BY - order by the order listed WHERE col IN ()

Posted on 2008-10-08
7
Medium Priority
?
258 Views
Last Modified: 2008-10-08
I need to order a recordset by the same order that I have them in the conditional clause of the query.

SELECT ID, Name FROM MyTable WHERE ID IN (5,3,6)

I need to have the results ordered in the same order as shown in the query - in this example that would be 5,3,6.
0
Comment
Question by:cgcmq
[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
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22669749
this will do:
SELECT t.ID, t.Name 
FROM MyTable t
JOIN ( SELECT 5 ID, 1 order_value UNION ALL select 3, 2 UNION ALL select 6,3 ) l
  ON t.ID = l.ID
ORDER BY l.order_value

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22669757
You can't really do it this way. you're best bet is to create a lookup table for the ID and give each ID a ranking.  Then join on that table and order by the ranking field.

SELECT ID, Name FROM MyTable m join lookup l on m.id = l.id WHERE ID IN (5,3,6)
order by l.ranking
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 22669859
Or for a lack of a join table.
SELECT
	CASE ID
		WHEN 5 THEN 1
		WHEN 3 THEN 2
		WHEN 6 THEN 3
	END AS Ordering,
	ID,
	Name
FROM
	MyTable
WHERE
	ID IN (5,3,6)
ORDER BY
	Ordering
	

Open in new window

0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22669873
What this will do is use custom sorting on your IN values.  In this case, you will only get those 3 ids, but I put in else to show how you would handle others.  By setting to 4 and then ordering by ID you will get your IN statement in order then others will sort by ID normally.
SELECT ID, Name 
FROM MyTable 
WHERE ID IN (5,3,6)
ORDER BY Case ID When 5 Then 1 When 3 Then 2 When 6 Then 3 Else 4 End, ID

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22669884
What if you have 10,000 IDs?  That is a big ORDER BY or CASE statement statement....if you have a lookup table, you update the ranking once, and your query is simple to write.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22669906
Yes, but that would be too easy!  

I was already typing and got hung up with something else.  After posted, I saw all the previous comments.  My solution was only meant for very limited case with static IN clause.  If this is going to be done all the time with variable inputs, lookup would make a lot more sense.
0
 

Author Comment

by:cgcmq
ID: 22669910
Thanks for all the input.  angelIII's solution works perfect and as it was first posted, and no one objects, I will go with that even though I am sure others will work as well.

SELECT t1.articleid, t1.headline, t1.summary, t2.name
FROM xlaANMarticles t1
LEFT OUTER JOIN xlaANMpublishers t2 ON t1.publisherid = t2.publisherid
JOIN ( SELECT 654 articleid, 1 order_value UNION ALL select 651, 2 UNION ALL select 659,3 ) l
  ON t1.articleid = l.articleid
ORDER BY l.order_value
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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