Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Example query

SELECT * FROM Table WHERE ID in (5,3,4)

How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third).

Edit - To be clear the ID's a dynamically generated list that is in the right order.

share|improve this question
up vote 6 down vote accepted

Your best bet is going to be to split the list into a temp table.

I'm using Jeff Moden's DelimitedSplit8k. You can find the code here. Note it includes an ItemNumber column that is the values in order.

DECLARE @DelimString varchar(50) = '4,5,3'

SELECT * INTO #DelimList FROM dbo.DelimitedSplit8k(@DelimString,',')

SELECT Table.*
FROM Table
JOIN #DelimList
    ON Table.Id = #DelimList.Item
ORDER BY #DelimList.ItemNumber

This has the added benefit of being faster for large numbers of values in the delimited list.

share|improve this answer

If you're generating that list, then try generating something like this instead:

JOIN (VALUES (5,1), (3,2), (4,3)) AS l(val, orderby)
  ON l.val = t.ID
ORDER BY l.orderby;

Or better still: generate an insert values statement for an indexed table or variable that avoids a sort for the order by clause. There will still likely be a sort for the insert of rows to the table or variable, but that will usually be cheaper/less likely to spill than in the main query.

share|improve this answer
    
This is another good approach - I image compared to the accepted answer , this would be quicker? – LiamB Jan 15 at 8:54
    
@LiamB - potentially, but Kenneth's answer is good too. I added it because it's a simpler answer. – Rob Farley Jan 15 at 12:07

Since the IDs are out of order (not DESCENDING or ASCENDING) I'd use UNION.

SELECT * FROM Table WHERE ID = 5
UNION ALL
SELECT * FROM Table WHERE ID = 3
UNION ALL
SELECT * FROM Table WHERE ID = 4

As a side note, using SELECT * in a UNION query isn't a good idea (and the rest of the time, it's debatable) - use the actual column names instead.

EDIT: It's been pointed out this won't necessarily work; and especially not with dynamic ID lists, like the edited question specifies.

share|improve this answer
4  
Putting them in separate queries like this won't help because it is in a single query you still can't guarantee the order without an ORDER BY clause. – Kenneth Fisher Jan 14 at 15:47
6  
Downvoted. While this technique may happen to return the rows in the desired order there is no guarantee unless an ORDER BY clause is coded. – Michael Green Jan 14 at 15:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.