Solved

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

Posted on 2008-10-08
7
254 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
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 59

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 59

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

829 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