Interesting SQL

I got the following mail from one of my classmates:

Hey Ganesh,

I need some help.
I need a SQL query to perform the following:

Following is the requirement. (Database – Oracle9i , Language - Java)

1. Consider there is a table Table1.

2. Records are to be obtained from table based on three columns. Let us call the columns as Column1, Column2 and Column3. Values for these will be provided by the user.

3. Following is the Rule
Records to be retrieved with the following criteria
1. Rule1 - Records where values given by user
Exactly match Column1 + Exactly match Column2 + Column3 contains the specified string.
2. Rule2 - Records where values given by user
Exactly match Column1 + Exactly match Column2
3. Rule3 - Records where values given by user
Exactly match Column1 + Column3 contains the specified string.
4. Rule4 - Records where values given by user
Column3 contains the specified string.

Records need to be ordered as above list, i.e records for point 1 should come first, for point 2 should come next and so on. And there shouldn’t be any duplicates.


I have a sample query but this will have duplicates (Empno’s selected in query 1 will be repeated in query 2 & 3, empno’s from 2 will be repeated in 3). Assume empno is key

select EMPNO, ename, 11 as colorder from emp where (sal = 100 and deptno = 10 and job = 'CLERK')
union
select EMPNO, ename, 22 as colorder from emp where (sal = 100 and deptno = 10)
union
select EMPNO, ename, 33 as colorder from emp where sal = 100
order by 3

I think the result from the above query can be easily manipulated by a program to remove duplicates. But I wanted try this in SQL itself and as efficiently as possible.
Let me know if you know a way to do this. Thanks.

And I mailed the following query for the same.. Not sure if there is a more sophisticated solution..

select empno,ename, case
when sal >2000 and deptno = 10 and job = 'CLERK' THEN
11
when sal>2000 and deptno = 10 then
22
when sal>2000 then
33
ELSE
0
end dummy
from emp

Cheers
Ganesh