February 23, 2007 at 12:45 pm
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
February 23, 2007 at 1:04 pm
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)
February 23, 2007 at 1:05 pm
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
February 23, 2007 at 1:35 pm
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)
February 23, 2007 at 2:00 pm
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