Re: Evaluation of SQL SELECT WHERE's

Hi, one more idea to throw in the mix (hope I'm posting in the right way;
let me know if not)

rank() may offer some advantages. I assume that if no one qualifies with
enough experience, and two people have the same years of college then job
experience would break the tie. Anway, cut and paste this into interactive
SQL and manipulate the values in the myfile CTE to see if it works for you:

with myfile( dept, name, years_experience, years_college, PMP)
as (values( 'X', 'Tom ', 8, 3, 'NO '),
( 'X', 'Dick ', 2, 5, 'YES'),
( 'X', 'Harry', 2, 4, 'YES'))
select dept, name, years_experience, years_college, PMP,
rank() over ( partition by dept order by
years_experience desc,
years_college desc ) as ye,
rank() over ( partition by dept
order by years_college desc ) as yc,
rank() over ( partition by dept order by
PMP desc, years_experience desc ) as pm
from myfile
where years_experience > 10
or years_college >= 4 or PMP = 'YES'
order by ye, yc, pm
fetch first 1 rows only

Arnie