MS SQL 2000 Query
Posted on 2009-04-12
I have two tables with one to many relationship namley employees (Emp) and qualifications (Qual). I want to select all employees with the most recent qualification. Each employee might have more than one qualification. The two tables are joined by the emp_cpr column. I tried the following SQL query :
SELECT Emp.CPR, Emp.name, Max(Qual.Date) AS Date
FROM Emp INNER JOIN Qual ON emp.cpr = Qual.cpr
GROUP BY Emp.cpr,emp.name
It's working only if I select two columns from the first table namely emp_cpr and emp_name. I want to group all employees by cpr showing only thier latest qualification.