Need help with Having and Max function

  • I am struggling with the HAVING clause trying to get a row selected using the max function.  I want to select the row with 2006 since it is the Max(Year) from the select below.  

     

    SELECT ItemId, MAX([Year]), AvgCost

     FROM         dbo.tblItemAvgCost

     WHERE     (AvgCost <> 0)

     GROUP BY ItemId, AvgCost

    gets me to this

    01-0036 2003 2.74

    01-0036 2004 2.60

    01-0036 2005 2.62

    01-0036 2006 2.52

    Now I need to select the bottom row from it.

     

    Thanks,

    vmon

  • without knowing your data, this query may get you what you want:

    SELECT ItemId, year, AvgCost

     FROM         dbo.tblItemAvgCost

     WHERE     (AvgCost <> 0)

     and year = (select max(year) from dbo.tblItemAvgCost where avgcost<>0)

  • HAVING MAX(YEAR) = 2006

    Looks like it should work...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Sorry, I might not be explaining what I am trying to do very well.  This might help show what I am trying to do.  I have 12 years for each item and I want to find the most recent row (year) with an AvgCost that is not zero.  I have the subquery get the set of rows that I then need to select the Max(year) row from.  it's making me crazy on a Friday.

    Thanks

     

    SELECT * FROM 

     (

     SELECT ItemId, [Year], AvgCost

     FROM         dbo.tblItemAvgCost

     WHERE     (AvgCost <> 0)

     GROUP BY ItemId, Year, AvgCost)

     a INNER JOIN tblItemAvgCost b ON a.ItemId = b.ItemId AND a.Year = b.Year

    GROUP BY ItemId, Year, AvgCost

    WHERE Max(year)

     

  • i am guessing that the avgcost field is already calculated. with that being said, this should help at least get you down a path:

    select orig.itemid, orig.year, orig.avgcost

    from

    (select itemid, year, avgcost

    from tblItemAvgCost

    where avgcost<> 0) orig

    inner join (select itemid, max(year) year

      from tblItemAvgCost

      where avgcost <>0) max_year

    on orig.itemid = max_year.itemid

    and orig.year = max_year.year

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply