someone wrote:
I have a stored procedure along the lines of
SELECT ID, Amount1, Amount2 ..
FROM Table1.
UNION
SELECT ID, Amount1, Amount2...
FROM Table2
ORDER BY 1
Is it possible to construct a conditional ORDER By clause that orders by Amount1 under certain conditions, and order by Amount2 under other conditions?
At the moment, the only way around it I have found (and I spent most of the night trying!) is to use an If clause and duplicate nearly all the SQL
IF (Condition1) THEN
SELECT ID, Amount1, Amount2 ..
FROM Table1.
UNION
SELECT ID, Amount1, Amount2...
FROM Table2
ORDER BY 1
ELSE IF (Condition2) THEN
SELECT ID, Amount1, Amount2 ..
FROM Table1.
UNION
SELECT ID, Amount1, Amount2...
FROM Table2
ORDER BY 2
This seems a crazy way of having to achieve my aim!
Tomasz Tyrakowski answers:
Try something like this:
select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1,
Amount2, ...
from Table1
union
select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1,
Amount2, ...
from Table2
order by 1
If there are more than two possible candidates for the ordering column, you can switch from iif to case - when - end.