July 15, 2004 at 9:48 am
I'm generating a query based on a querystring which effectively produces the following query: -
"SELECT * FROM mytable WHERE Id IN (1,3,2,4);"
(note the IN clause is not in sequntial order)
Currently it defaults the recordset to the order of the Id field. I.e.
1
2
3
4
Is there anyway I can Order the returning recordset by the order of the Id's in the IN clause? I.e
1
3
2
4
Cheers
Dan
July 15, 2004 at 10:02 am
SELECT * FROM mytable WHERE Id = 1
union all
SELECT * FROM mytable WHERE Id = 3
union all
SELECT * FROM mytable WHERE Id = 2
union all
SELECT * FROM mytable WHERE Id = 4
July 15, 2004 at 2:45 pm
SELECT * FROM mytable WHERE Id IN (1,3,2,4)
ORDER BY CASE Id WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 3 WHEN 4 THEN 4 ELSE 0X7FFFFFFF END
..by the way Allen, does UNION ALL guarantee the ordering of the result ?
/rockmoose
You must unlearn what You have learnt
July 16, 2004 at 7:38 am
Good question. I don't see why not if there is no ORDER BY.
July 16, 2004 at 3:55 pm
I examined some Execution plans, and what happens when using union all is that a "Concatenation" takes place.
BOL very helpfully gives this:
Concatenation
The Concatenation logical and physical operator scans multiple inputs, returning each row scanned.
But I haven't come up with an answer to whether this will keep the ordering in the resultset.
Empirically, Yes it does.
But I have not found any proof/documentation that the different resultsets between "union all" are all returned in the "correct" order to the concatenation operator.
Oh Well...
/rockmoose
You must unlearn what You have learnt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply