|
|
Posted by Hugo Kornelis on 08/22/06 21:39
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?
Hi ree321,
Try if this works:
SELECT TOP 10 a.Project, SUM(a.Amount) AS TotalAmount
FROM YourTable AS a
WHERE a.Date = (SELECT MAX(b.Date)
FROM YourTable AS b
WHERE b.Project = a.Project)
GROUP BY a.Project
ORDER BY TotalAmount DESC
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|