#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    7
    Rep Power
    0

    Question 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
    Last edited by rizzo; November 25th, 2003 at 11:41 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    SELECT *
    FROM myquery join 
    (SELECT smachName x, MAX(ScanID) y
    FROM myquery
    GROUP BY smachName) dt
    on smachName = x
    and  ScanID = y
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    7
    Rep Power
    0
    Thanks! I'll play with that.

    Also, I think I may need the DISTINCT function?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    7
    Rep Power
    0
    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!!!

IMN logo majestic logo threadwatch logo seochat tools logo