Solved

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

Posted on 2008-10-08
7
255 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
Technology Partners: 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!

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need time in SQL 8 29
SQL Add Parameter in Variable 4 21
SQL Get Store Procedure Column Name As Row 5 38
Access #Deleted data 20 34
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

680 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