sql, selecting the lastest completed couse using max (column name) not working

Question

Every year employee takes mandatory courses. I need to get a list of employees and their latest completed course, for example FS (fire safety). Below is my query. The problem is it shows two or three completed courses for some employees.

Trying to troubleshoot those employees’s record, it shows the latest completed course, when I select only one employee explicitly on employee id. What am I doing wrong?

Select 
h.empno,
p.p_lname,
p.p_fname,
h.classid,
h.course,
h.certcode,
h.startdate,
h.enddate,
h.enrstat
from dbo.nemphist h
 join dbo.hrpersnl p
  on h.empno=p.p_empno
where   h.course='FS'and
--        p.p_empno='99999123'and 
        p.p_active<>'T' and
        p.p_company='XXX'and  
        h.enrstat='COMP' and
        h.startdate in  
   (Select max(startdate)
    from dbo.nemphist h
    join dbo.hrpersnl p
    on h.empno=p.p_empno  
--            h.company=p.p_company
   where h.course='FS'and
--                  p.p_empno=''99999123''and
         p.p_active<>'T' and
         p.p_company='XXX' and 
         h.enrstat='COMP'
   Group by h.empno)
order by p.p_lname

I have corrected my query.

Answer 1

 h.startdate in   
   (Select h.empno,  max(startdate) 
    from dbo.nemphist h 
    join dbo.hrpersnl p 
    on h.empno=p.p_empno   
--            h.company=p.p_company 
   where h.course='CPR/FA'and 
--                  p.p_empno=''99999123''and 
         p.p_active<>'T' and 
         p.p_company='XXX' and  
         h.enrstat='COMP' 
   Group by h.empno) 

If two different employees have the same startdate for different courses you could get wrong results here when you are not selecting a specific employee.

Make a derived table and join on startdte and empno instead.