Get rows in order predicates appear in where clause? 1

[Coder7]

Is there a way to get the rows returned in the order in which the predicates appear in the where clause IE

THE QUERY:
select PRED from TABLEA
where PRED = 9
or PRED = 21
or PRED = 3

THE RESULT SET:
9
21
3

Thanks for any insight!

 

[LogicalmanUS]

Coder7,

I cannot see how you can determine a different sort on the same column in one pass.
This can be done using a SProc, and the values would need to be passed as either a string of values (like an array) or as seperate params, due to the requirement to order by.
Using the SQL statement as above, load a temp table with the results you want. Because the order by may not be in numerical order, you will need to UNION result sets together to get the right order.
e.g.
select PRED from #TempTABLEA where PRED = @param1
UNION ALL
select PRED from #TempTABLEA where PRED = @param2
UNION ALL
select PRED from #TempTABLEA where PRED = @param2


(this would be where @param1=9, @param2=23, @param3=3)

Hope this assists

Logicalman

 

[sunila7]

Hi,

Try this query....

select PRED from TABLEA
where PRED = 9
or PRED = 21
or PRED = 3
Order By Case PRED When 9 then 1
When 21 then 2
When 3 then 3
End

Hope it helps

Sunil

 

[Coder7]

Thanks for the input LM and Sunila.

Sunila's method seems less complex - earns you a star.

 

[SQLSister]

Sunila's methos is also more efficient.

 

[Coder7]

Thanks for that info, SQL Sister.

I'm always looking for ways to improve performance.

Have a great week everyone!!