SQL Question

[tunjio]

EMPLOYEE

EMPLID NAME
6001 John Doe
6002 Mae Ellen

ACCOUNT
EMPLID Date Account Type
6001 01/02/2005 A
6001 05/02/2005 A
6002 08/02/2005 B
6002 09/02/2005 B

I want a query that will give me the latest row (based on date) for each employee and by the account type.


The output of my query should be similar to below:

The tables are joined on Employee ID

EMPLID NAME DATE ACCOUNT TYPE
6001 John Doe 05/02/2005 A
6002 Mae Ellen 09/02/2005 B

 

[lewisp]

SELECT e.emplid,
       e.name,
       a.adate,
       a.account_type
FROM   employee e,
      (SELECT MAX(date) adate,
              emplid,
              account_type
       FROM   account
       GROUP BY emplid,
                account_type) a
WHERE  e.emplid = a.emplid;