Solved

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

Posted on 2008-10-08
7
250 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 142

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:RQuadling
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now