GROUP BY, Two Tables: One Unique, One Not... 1

[bubbisthedog]

Hello, all. First post. Following are my table structures ("*" indicates primary keys):

TA: EM_ID*, FNAME, LNAME (unique entries)
TB: EM_ID, DP_ID, SDATE (not unique entries)
TC: DP_ID*, DESC (unique entries)

As you can see, TB is my intermediate table, and can include non-unique EM_IDs and DP_IDs. I am seeking to retrieve records that contain

1) Unique EM_IDs (whereupon I can get unique first and last names) based on the most recent department that employees work in (MAX(TB.SDATE) would accomplish this);
2) The employees' departments corresponding to the record containing MAX(TB.SDATE).

RESULT:

FNAME | LNAME | DESC | MAX(SDATE)

I have not been able to accomplish this because GROUP BY requires that I use

SELECT TB.EM_ID, TB.DP_ID, MAX(TB.SDATE)
FROM TB
GROUP BY TB.EM_ID, TB.DP_ID

That is, optimally, I'd like to have

SELECT TB.EM_ID, TB.DP_ID, MAX(TB.SDATE)
FROM TB
GROUP BY TB.EM_ID

which makes sense to me; but SQL does not allow it. I hope this all makes some sense. I'd greatly appreciate help getting the results that I'm seeking.

Thank you very much in advance,

bubbis

 

[PHV]

SELECT B.EM_ID, B.DP_ID, B.SDATE
FROM TB B INNER JOIN (
SELECT EM_ID, MAX(SDATE) LastDate FROM TB GROUP BY EM_ID
) M ON B.EM_ID = M.EM_ID AND B.SDATE = M.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bubbisthedog]

PH,

That's friggin' amazing how fast you did that. I've constructed nearly identical derivatives of your statement trying to get it to work, but was apparently missing the AND part. You've helped me a great deal, PH. Thank you very much for your kindness and unbelievable response time.

Regards,

bubbis