|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
|
|||
|
|||
|
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 Last edited by rizzo : November 25th, 2003 at 10:41 AM. |
|
#2
|
|||
|
|||
|
Code:
SELECT * FROM myquery join (SELECT smachName x, MAX(ScanID) y FROM myquery GROUP BY smachName) dt on smachName = x and ScanID = y |
|
#3
|
|||
|
|||
|
Thanks! I'll play with that.
Also, I think I may need the DISTINCT function? |
|
#4
|
|||
|
|||
|
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!!! |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > need help with query! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|