Posted by Ed Murphy on 08/22/06 08:49

On 21 Aug 2006 23:26:25 -0700, ree321@gmail.com wrote:

>Say you have table with that is filled with the amount of dollars for a
>project for given dates.
>
>i.e. 3 columns - Project, Amount, Date
>
>A project could have multiple entries in the tabke but this will vary
>depending on the Date
>
>How would you query the top 10 projects in regards to most amount of
>dollars where each project has to use the latest date in regards to
>amount of dollars.
>
>I tried to use Group By but got stumped as I didn't know how to group
>Amount using the Max(Date).
>
>So does anyone know how to achieve this?

create view v1 as
select project, max(date) as max_date
from t
group by project

go

create view v2 as
select t.project, sum(t.amount) as sum_amount
from t join v1 on t.project = v1.project and t.date = v1.max_date
group by t.project

go

select top 10 project
from v2
order by sum_amount

(You can probably collapse some of this using compound queries
if you want.)

[Back to original message]