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