SubQuery Question - Microsoft SQL Server

Hello Everyone, i have a rather chunky Query:

Expand|Select|Wrap|Line Numbers

	SELECT top 100 percent EMDET.DET_NUMBERA, EMDET.DET_G1_NAME1A, EMDET.DET_SURNAMEA, EMDET.DET_BIR_DATED, EMDET.DET_SFT_DATED, EMPOS.POS_PDT_GRDA, EMPOS.POS_ENDD, EMPOS.POS_AV_HR_WKN, EMPOS.POS_L3_CDA, Max(EMSAL.SMN_DATEC) AS SMN_DATEC, Max(EMSAL.SMN_HOURLYN) as SMN_HOURLYN, EMPOS.POS_COST_GRPA, EMDET.DET_TER_DATED

FROM EMDET, EMPOS, EMSAL

WHERE EMDET.DET_NUMBERA = EMPOS.DET_NUMBERA AND EMDET.DET_NUMBERA = EMSAL.DET_NUMBERA AND EMPOS.DET_NUMBERA = EMSAL.DET_NUMBERA

GROUP BY EMDET.DET_NUMBERA, EMDET.DET_G1_NAME1A, EMDET.DET_SURNAMEA, EMDET.DET_BIR_DATED, EMDET.DET_SFT_DATED, EMPOS.POS_PDT_GRDA, EMPOS.POS_ENDD, EMPOS.POS_AV_HR_WKN, EMPOS.POS_L3_CDA, EMPOS.POS_COST_GRPA, EMDET.DET_TER_DATED

HAVING (EMPOS.POS_ENDD Is Null) AND (EMPOS.POS_L3_CDA<>'002' And EMPOS.POS_L3_CDA<>'004' And EMPOS.POS_L3_CDA<>'023' And EMPOS.POS_L3_CDA<>'024' And EMPOS.POS_L3_CDA<>'601' And EMPOS.POS_L3_CDA<>'701') AND (EMPOS.POS_COST_GRPA<>'MA' And EMPOS.POS_COST_GRPA<>'RM' And EMPOS.POS_COST_GRPA<>'PM' And EMPOS.POS_COST_GRPA<>'HO') AND (EMDET.DET_TER_DATED Is Null)

ORDER BY EMPOS.POS_L3_CDA, EMDET.DET_SURNAMEA, Max(EMSAL.SMN_DATEC) DESC

This returns the following alot of data, but the 2 columns i am interested in are SMN_DATEC and SMN_HOURLYN, and the foreign key which is DET_NUMBERA

Example data in these columns is:
SMN_DATEC: 2008-05-01 00:00:00
SMN_HOURLYN: 7.9028
DET_NUMBERA: 0002379

This is selecting the max HOURLY and DATEC, this logic however is not correct, The HOURLYN needs to be selected where its on the same row as the MAX(SMN_DATEC) in the EMSAL table. For example, for DET_NUMBERA '0002379' we can get the data by running the following:

Expand|Select|Wrap|Line Numbers

	SELECT SMN_DATEC, SMN_HOURLYN FROM db_Chris21_Live."COHENSCHEMIST\chriscs".EMSAL WHERE DET_NUMBERA = '0002379' Order by SMN_DATEC DESC

Which produces the following results, the row i am after is the latest date. (top 1)

Expand|Select|Wrap|Line Numbers

	
2008-05-01 00:00:00.000    6.98

2008-04-01 00:00:00.000    7.9028

2007-09-01 00:00:00.000    6.81

2007-06-01 00:00:00.000    6.68

2007-04-01 00:00:00.000    7.71

2006-08-01 00:00:00.000    6.5

2006-07-03 00:00:00.000    7.5

Note the max date is 1st May2 2008.... which has an hour rate of 6.98, you can see in the original results i got 7.9 (because we where only using MAX) so how do we edit the original query to select the Hourly rate based on MAX date?

PS: sorry for the long over complicated query, i was just trying to offer as much info as possible.
SQL SERVER: 2000 (8.0.760) by the way, and there is no primary key in the EMSAL table, just the DET_NUMBERA foreign key (its an off the-shelf product so i cant just go adding my own primary key etc)