Aggregate function returns duplicate values

[lokiluke]

Hi all,



i have a query:

SELECT tblAssets.AssetID, MAX(tblTests.TestDate)
FROM tblAssets LEFT JOIN tblTests ON tblAssets.AssetID = tblTests.AssetID
GROUP BY tblAssets.AssetID;

which returns the latest date for each asset in tblAssets that is recorded in tblTests. there can be multiple entries in tblTests for each asset in tblAssets linked by AssetID field.

this all works well (thanks Alan), but now i want to add tblTests.TagNumber to the query. when i add that field to the SELECT line it then returns all the values from tblAssets.

i have tried using multiple joins but cant seem to get them to work.

can anyone point me in the right direction.

cheers,

luke

[lokiluke]

i have tried using this join but cant seem to find the reason for the error.

SELECT tblAssets.AssetID, MAX(tblTests.TestDate), tags.TagNumber
FROM tblTests RIGHT JOIN (tblAssets LEFT JOIN (SELECT tblTests.TagNumber FROM tblTests ) tags ON tblTests.AssetID=tblAssetID) ON tblAssets.AssetID = tblTests.AssetID
GROUP BY tblAssets.AssetID;

i get a 'syntax error in JOIN operation'

any ideas?

cheers,
luke

[lokiluke]

ok i have managed to get it to not return an error but it is still returning multiple entries for each AssetID, am i at least on the right track?

SELECT tblAssets.AssetID, MAX(tblTests.TestDate), tags.TagNumber
FROM tblTests RIGHT JOIN (tblAssets LEFT JOIN (SELECT tblTests.TagNumber, tblTests.AssetID FROM tblTests) tags ON tags.AssetID=tblAssets.AssetID) ON tblAssets.AssetID = tblTests.AssetID
GROUP BY tblAssets.AssetID, tags.tagnumber;

[lokiluke]

Eureka! amazing what can happen when you take a short break

i have managed to solve it using:

maxdate is:

SELECT tblAssets.AssetID, MAX(tblTests.TestDate) AS TestDate
FROM tblAssets LEFT JOIN tblTests ON tblTests.AssetID = tblAssets.AssetID
GROUP BY tblAssets.AssetID

SELECT maxdate.AssetID, maxdate.TestDate, tblTests.TagNumber
FROM maxdate LEFT JOIN tblTests ON (tblTests.AssetID=maxdate.AssetID AND tblTests.TestDate=maxdate.TestDate)

now my question is; is this the best way to do this with two queries? or is there a single query i can use?