November 28, 2006 at 3:35 pm
I have a table with many groups of items as follows:
Item Locn Sales #months
35140 TP 100 8
35140 SF 556 11
35140 LA 320 2
I would like to SELECT rows from this table and calculate a max(#months) value for each group of items and add it to each row of the group, e.g.
Item Locn Sales #months max(#months)
35140 TP 100 8 11
35140 SF 556 11 11
35140 LA 320 2 11
Can you suggest a simple way of doing this without using a stored procedure?
November 28, 2006 at 3:39 pm
select *
from tablea
cross join ( select max( months ) as max_months from tablea ) w
November 28, 2006 at 3:41 pm
Join to a derived table that aggregates the required MAX() data:
Select t.Item, t.Locn, t.Sales, t.[#Months], dt.MaxMonths
From YourTable As t
Inner Join
(
Select Item, Max( [#Months] ) As MaxMonths
Group By Item
) dt
On (t.Item = dt.Item)
November 28, 2006 at 3:47 pm
Thanks, you guys are not only good -- but quick!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply