Solved

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

Posted on 2008-10-08
7
256 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 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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, show how to shrink a transaction log file down to a reasonable size.
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.

740 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