Alejandro Mesa
3/30/2007 8:34:00 PM
Marc Miller,
Why year 1968 for project ABC?
The highest amount for project ABC is 80.00 and belong to account 3234,
which has just one row and which year is 1951.
Also, what do you expect if more than one account, in the same project,
share the highest amount?
;with cte
as
(
select
Project, Account, [Year], Amount,
rank() over(partition by Project order by Amount DESC) as rn_1,
row_number() over(partition by Project, Account order by [Year] DESC) as rn_2
from
dbo.t1
)
select
a.Project, a.Account, a.Amount, b.[Year]
from
cte as a
inner join
cte as b
on a.Project = b.Project
and a.Account = b.Account
and a.rn_1 = b.rn_2
and a.rn_1 = 1
and b.rn_2 = 1
order by
a.Project,
b.[Year]
go
AMB
"Marc Miller" wrote:
> I've scratched my brain raw and searched the net. Maybe someone here
> will be kind enough to help.
>
> I have a table:
>
> Project Account Year Amount
>
> ABC 1234 1922 10.00
> ABC 1234 1944 20.00
> ABC 2234 1966 50.00
> ABC 2234 1968 55.00
> ABC 3234 1951 80.00
> DEF 1234 1946 90.00
> DEF 1234 1944 10.00
> DEF 2234 1950 20.00
>
> I need to determine which account has the most dollars and within that
> account
> what is the most current year. The project and that year is what I need for
> the
> ultimate result set. In the example above I would end up with:
>
> Project Year
> ABC 1968
> DEF 1946
>
> Your collective help is greatly appreciated,
> Marc Miller
>
>
>
>
>
>
>
>
>
>