I only want the record with the largest FTE, but I don’t want the FTE in the output.
example:
Select a, b, c from table where x = MAX(x)
Data:
a b c x
---------------------
sam 111 aaa 1
sam 222 bbb 2
sam 333 ccc 3
bob 555 eee 8
bob 222 bbb 4
bob 999 zzz 6
Output:
sam 333 ccc
bob 555 eee
The following seems to work, at least I think it does, so how can I get the result w/o MAX(prep_fte_pct) in the output?
SELECT M.prem_emp AS EMPLOYEEID,
M.prem_p_jclass AS JOBCLASS,
M.prem_p_bargain AS BARGAINING,
M.prem_loc AS DEPARTMENT,
MAX(prep_fte_pct)
FROM prempmst M, premppay P
WHERE M.prem_proj = P.prep_proj
AND M.prem_emp = P.prep_emp
AND M.prem_proj = 0
AND M.prem_emp = 91
AND P.prep_pay BETWEEN 100 AND 199
GROUP BY M.prem_emp,M.prem_p_jclass,M.prem_p_bargain,M.prem_loc
example:
Select a, b, c from table where x = MAX(x)
Data:
a b c x
---------------------
sam 111 aaa 1
sam 222 bbb 2
sam 333 ccc 3
bob 555 eee 8
bob 222 bbb 4
bob 999 zzz 6
Output:
sam 333 ccc
bob 555 eee
The following seems to work, at least I think it does, so how can I get the result w/o MAX(prep_fte_pct) in the output?
SELECT M.prem_emp AS EMPLOYEEID,
M.prem_p_jclass AS JOBCLASS,
M.prem_p_bargain AS BARGAINING,
M.prem_loc AS DEPARTMENT,
MAX(prep_fte_pct)
FROM prempmst M, premppay P
WHERE M.prem_proj = P.prep_proj
AND M.prem_emp = P.prep_emp
AND M.prem_proj = 0
AND M.prem_emp = 91
AND P.prep_pay BETWEEN 100 AND 199
GROUP BY M.prem_emp,M.prem_p_jclass,M.prem_p_bargain,M.prem_loc