Solved

MS SQL 2000 Query

Posted on 2009-04-12
3
211 Views
Last Modified: 2012-05-06
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.

Thanks
0
Comment
Question by:AZZA-KHAMEES
  • 2
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24126215
SELECT e.cpr, q.QualDescr --guessing on the column name here!
FROM Emp e INNER JOIN
      Qual q ON e.cpr = q.CPR INNER JOIN
      (SELECT q2.cpr, Max(q2.Date) AS MaxDate
      FROM Qual q2
      GROUP BY q2.cpr) z ON q.cpr = z.cpr AND q.Date = z.MaxDate
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24126220
Sorry, make that


SELECT e.cpr, e.Name, q.QualDescr --guessing on the column name here!
FROM Emp e INNER JOIN
      Qual q ON e.cpr = q.CPR INNER JOIN
      (SELECT q2.cpr, Max(q2.Date) AS MaxDate
      FROM Qual q2
      GROUP BY q2.cpr) z ON q.cpr = z.cpr AND q.Date = z.MaxDate
0
 

Author Closing Comment

by:AZZA-KHAMEES
ID: 31569339
Thanks alot. It's working fine
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 47
Updating a table from a temp table 4 28
How to use Full Text CONTAINS with Case in SQL 6 18
T-SQL: Nested CASE Statements 4 24
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now