SQL Select Max Help?
 

Hello,

Hoping someone can help me with this...I'm pulling data from 2 tables: Employment and Job. The Job table has multiple effective dates (effdt), so when I run the statement, I'm getting ALL the rows, and I want only the most recent record. I've been playing around with "select max" and I can't seem to get it right....can you?? Also, I had to add distinct to the select, otherwise each effdt was appearing twice. Thanks in advance for your help!

select distinct e.emplid, e.check_voucher_ind, j.empl_status, j.effdt
from ps_employment e, ps_job j
where e.emplid = j.emplid
and j.emplid = '105917'
order by check_voucher_ind

Results are:
EMPLID Check_voucher_ind Status effdt
105917 N A 11/03/2003 00:00:00
105917 N A 06/03/2004 00:00:00
105917 N A 07/03/2004 00:00:00
105917 N A 07/02/2005 00:00:00
105917 N A 09/28/2005 00:00:00
105917 N A 12/05/2005 00:00:00
105917 N A 07/01/2006 00:00:00
105917 N A 07/07/2006 00:00:00
105917 N A 09/19/2006 00:00:00
105917 N A 11/04/2006 00:00:00
105917 N A 06/30/2007 00:00:00
105917 N P 05/18/2004 00:00:00

select distinct e.emplid, e.check_voucher_ind, j.empl_status, j.effdt
from ps_employment e, ps_job j
where e.emplid = j.emplid
and j.emplid = '105917'
and j.effdt =(select max(j2.effdt) from ps_job j2 where j.emplid = j2.emplid)
order by check_voucher_ind

The simple solution is to put a "LIMIT 1" on the end of that statement. You lose some efficiency, but you get exactly your results. I wouldn't put that anywhere it's liable to be run more than once a second, or anywhere transaction volume is high.

If you can do sub-selects, you can:
SELECT ... FROM ps_employment WHERE effdt = (SELECT MAX(effdt) FROM ps_employment);

That's the elegant solution. But not all databases (I'm looking at you, older versions of MySQL) support subselects.

Bear in mind that the sub-select option isn't guaranteed to get you just one record unless your effdt field is defined UNIQUE (or guaranteed unique in code).

that did it! thanks a lot Chilly!! :thumbsup: I see how you aliased ps_job again....clearly I have much to learn about SQL.

ratbastid - it's an oracle ADP database. antiquated would be a nice way of putting it. I only run this once every 2 weeks to capture selections on people electing paperless payroll...thanks for your input!

No probs mike, sorry I didn't think to ask if your database supported sub queries, I only ever use Oracle 9i or 10g so it didn't even occur to me!

edited: nevermind, ADP finally helped me. there were other tables to factor in..... if you're curious:

select e.emplid, e.check_voucher_ind, j.empl_status, j.effdt
from ps_employment e, ps_job j
where e.emplid = j.emplid
and j.effdt =(select max(j2.effdt) from ps_job j2 where j.emplid = j2.emplid)
AND J.EFFDT = (SELECT MAX(JJ.EFFDT) FROM PS_JOB JJ WHERE JJ.EMPLID = J.EMPLID
AND JJ.EMPL_RCD_NBR = J.EMPL_RCD_NBR
AND JJ.EFFDT <= SYSDATE)
AND
J.EFFSEQ = (SELECT MAX(JJJ.EFFSEQ) FROM PS_JOB JJJ WHERE JJJ.EMPLID = J.EMPLID
AND JJJ.EMPL_RCD_NBR = J.EMPL_RCD_NBR
AND JJJ.EFFDT = J.EFFDT)
order by check_voucher_ind