Select Most Recent record 2

[TitleistDBA]

I have a table that has multiple clocknum's. I am looking for the most recent record for each clocknum. I am trying to get it by a datetime field called EFFDT. For some reason I am only getting back one record. Shouldn't this query return me a record for each clocknum?

SELECT a.clocknum, a.EFFDT
FROM employee_dump_tbl a, employee_dump_tbl b
WHERE a.clocknum = b.clocknum
and a.EFFDT = (SELECT MAX(EFFDT) from employee_dump_tbl)
and empl_action not in ('REC')

Thanks for the help.

http://www.acushnetcompany.com

http://www.titleist.com

 

[r937]

SELECT clocknum
     , EFFDT
  FROM employee_dump_tbl as ZZ
 where EFFDT 
     = ( SELECT MAX(EFFDT) 
           from employee_dump_tbl
          where empl_action <> 'REC'
            and clocknum = ZZ.clocknum )
   and empl_action <> 'REC'

r937.com | rudy.ca

 

[TitleistDBA]

Thanks r937! That's it.

http://www.acushnetcompany.com

http://www.titleist.com