need help with query!

Hi, this is my second day SQLing and I have been watching your form and was hoping someone could help me with a query.

I can do the query I need on a simple table....

(example table)
name num
---------------
a 1
a 2
a 3
b 3
b 6

SELECT DISTINCT name, MAX(num)
FROM dbo.testtable
GROUP BY name

the above query returns;
a 3
b 6

SOOO, below is the query I currently have and need to add the MAX funtionality.
I think I need to use the MAX and DISTINCT functions to filter the display with the following fields DISTINCT(dbo.ScanMachines.smachName) and MAX(dbo.Scans.ScanID). So if the query below was called 'MYQUERY'


what i am trying to achieve would be the result of this...err if it worked. /


SELECT *
FROM myquery
WHERE (smachName, ScanID IN
(SELECT DISTINCT smachName, MAX(ScanID)
FROM myquery
GROUP BY smachName))


--------------------
--- MY QUERY ---
--------------------
SELECT dbo.Scans.ScanDate, dbo.Scans.ScanID, dbo.ScanMachines.smachName, dbo.ScanMachines.smachIPAddress,
dbo.Products.prodName, dbo.Patches.patchQNumbers, dbo.ScanItems.itemType, dbo.Patches.patchBulletinID, dbo.LinkSPProduct.spplOrdinal,
dbo.ItemTypes.itName
FROM dbo.Products INNER JOIN
dbo.Scans INNER JOIN
dbo.ScanMachines ON dbo.Scans.ScanID = dbo.ScanMachines.smachScanID INNER JOIN
dbo.LinkPatchProduct INNER JOIN
dbo.Patches ON dbo.LinkPatchProduct.pspplpatchID = dbo.Patches.patchID INNER JOIN
dbo.LinkSPProduct ON dbo.LinkPatchProduct.pspplspplID = dbo.LinkSPProduct.spplID ON
dbo.ScanMachines.smachOSID = dbo.LinkSPProduct.spplID ON dbo.Products.prodID = dbo.LinkSPProduct.spplprodID INNER JOIN
dbo.ScanItems ON dbo.Scans.ScanID = dbo.ScanItems.itemScanID AND dbo.ScanMachines.smachID = dbo.ScanItems.itemMachineID AND
dbo.LinkPatchProduct.pspplID = dbo.ScanItems.itempspplID AND dbo.LinkSPProduct.spplID = dbo.ScanItems.itemspplID INNER JOIN
dbo.ItemTypes ON dbo.ScanItems.itemType = dbo.ItemTypes.itKey
ORDER BY dbo.ScanMachines.smachName, dbo.Patches.patchQNumbers


Any ideas?

Thanks!

da rizzo

Thanks! I'll play with that.

Also, I think I may need the DISTINCT function?

DUDE U ROCK!!!

Here's how it was cooked!!!!

SELECT *
FROM dbo.ReportPatchesbyServerV INNER JOIN
(SELECT DISTINCT smachName x, MAX(ScanID) y
FROM dbo.ReportPatchesbyServerV
GROUP BY smachName) dt ON dbo.ReportPatchesbyServerV.smachName = dt.x AND dbo.ReportPatchesbyServerV.ScanID = dt.y

THANKS AGAIN!!!