Query help

[sonaliak]

This is just a part of a query, the actual query has a join and some other stuffs.

SQLWKS> select work_id, work_level from work
2> where work_level in(0,1)
3> order by work_level asc
4>
WORK_ID WORK_LEVEL
---------- ----------
737 0
734 0
826 0
1466 0
1544 0
2667 0
2220 0
1543 0
1234 0
1 1
2 1

What I want to do is I want to select work_level in certain order and do not want to get rest of the records.
The order is say work_level = 1, 0, 2, 5, 7, 9 (which is fixed but not serial) so I cannot use GREATEST or LEAST functions.

In the above example it should display only records with work_level = 1 and not =0.
Suppose there are no records with work_level = 1 then should go for work_level = 0 , if not then go for work_level = 2 if not then go for work_level = 5.
So it is a OR clause but with specific order and the processing should stop after it finds the match for the 1 st in the sequence.
Does any one know a query which will do this ??

Thanks
Sonali

[lesstjm]

I am not sure but if you were to use the decode like this:

select work_id, work_level from work
where work_level = DECODE(work_level,1,1,DECODE(work_level,0,0,DECODE(work_level,2,2,DECODE(
work_level,5,5,DECODE(work_level,7,7,9)))));

Let me know what happens.

[sonaliak]

Thanks, I had tried using DECODE too.
YOur query also does not work, it displays all !
here is part of the result set:
WORK_ID WORK_LEVEL
---------- ----------
1 1
35 2
60 2
747 5
141 2
737 0
734 0
236 2
256 2
547 5
553 2
542 5
543 5
544 5
546 5
607 2
705 5
721 5
664 2
667 5
668 5
669 5
672 5
697 2
698 5
803 2
805 2
820 5
826 0
6101 5
6102 7
6103 5
6124 5
6125 5
6127 5
6141 5
6149 7
6150 7
6151 7
6152 7
6153 7
6163 5
6803 5
6808 5
6816 5
6822 5
7090 5
7096 2
7349 5
7350 5
..................
9460 5
9480 2
9520 5
9561 9
9563 5
9565 7
9580 5
9581 5
9601 5
9641 7
9661 5
9663 7
9720 2
9721 5
9780 2
9824 2
9840 2
1120 rows selected.

[jgmagnus]

This is probably not efficient, but it gets the results you want.

select work_id, work_level from work
where work_level = 1
UNION
select work_id, work_level from work
where work_level = 0
UNION
select work_id, work_level from work
where work_level = 2
UNION
select work_id, work_level from work
where work_level = 5
UNION
select work_id, work_level from work
where work_level = 7
UNION
select work_id, work_level from work
where work_level = 9;

- Magnus

[jmodic]

Sonaliak,

This will probably get you what you want (provided you are using 8i):

SELECT work_id, work_level FROM work
WHERE work_level =
(SELECT work_level FROM
(SELECT work_level FROM work
ORDER BY DECODE(work_id, 1,1, 0,2, 2,3, 5,4, 7,5, 9,6)
);

HTH,