SubQuery Question

[HeavenCore]

Hello Everyone, i have a rather chunky Query:

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:

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)

[B]2008-05-01 00:00:00.000	6.98[/B]
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)

[ck9663]

Sorry, I easily gets confused with queries. I can be of more help seeing the source table(s) and your desired result.

-- CK

[balabaster]

You know you could simplify the query a little if you used the NOT IN expression:

...
AND EMPOS.POS_L3_CD A Not In ('002','004','0 23','024','601' ,'701')
AND EMPOS.POS_COST_ GRPA Not In ('MA','RM','PM' ,'HO')
...

Performance shouldn't be impacted noticeably (although, it should only improve) but that's gotta be easier to read than all those brackets and <>