Visual studio - find maximum dollar amount in a field for each year

I have a table in visual studio that lists nonprofit grant recipients for a number of years.

How can I automatically pull the largest grant for each year.  For example, my results would show Nonprofit2 with the amount for 2006 and Nonprofit5 with the amount for 2007.

I assume it's some kind of command with MAX, but I'm not sure how to write it.

Recipient           Amount        Year
Nonprofit1          $4,000         2006
Nonprofit2          $8,000         2006
Nonprofit3          $2,000         2006
Nonprofit4          $1,000         2007
Nonprofit5          $2,000         2007
TinaSCAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
whats rthe datatype of Amount ? if it is money  use

select u.*
from yourTable  u
inner join  (selet max(amount) mamount , year from yourTable a group by Year ) as a
on a.mamount = u.amount and a.year  = u.year
0
 
TinaSCAuthor Commented:
 
Thanks.  Amount is money.  

I sort of understand what you have, but Im working with a complicated view to pull the fields and am struggling a bit with how to insert your suggestion.

Recipient (V_GRHistory.RecipientName), Amount (V_GRHistory.AdjustedAmount AS PayAmount) , and Year (YEAR(V_GRHist1.DatePaid) AS Year).  (I probably should have included that the first time).  

Can you help me incorporate your suggestions into this view (code attached)?  

SELECT     
GRCharacteristics.Name, GRCharacteristicCodes.CodeValue, V_GRHistory.RecipientName, V_GRHistory.DatePaid, V_GRHistory.AdjustedAmount AS PayAmount, V_GRHistory.PayStatus, V_GRHistory.FundNumber, YEAR(V_GRHist1.DatePaid) AS Year

FROM         
V_GRHist1  
INNER JOIN GRGrantCharacteristics 
INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId 
LEFT OUTER JOIN GRCharacteristicCodes ON GRGrantCharacteristics.CharacteristicCodeId = GRCharacteristicCodes.CharacteristicCodeId 
LEFT OUTER JOIN GRCharacteristics ON GRGrantCharacteristics.CharacteristicId = GRCharacteristics.CharacteristicId

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is GrantId the primary key for V_GRHist1 ?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
TinaSCAuthor Commented:
Yes.
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT    
GRCharacteristics.Name, GRCharacteristicCodes.CodeValue, V_GRHistory.RecipientName, V_GRHistory.DatePaid, V_GRHistory.PayStatus, V_GRHistory.FundNumber,
YEAR(V_GRHist1.DatePaid) AS Year, V_GRHistory.AdjustedAmount AS PayAmount
FROM        
V_GRHist1  
INNER JOIN GRGrantCharacteristics
INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId
LEFT OUTER JOIN GRCharacteristicCodes ON GRGrantCharacteristics.CharacteristicCodeId = GRCharacteristicCodes.CharacteristicCodeId
LEFT OUTER JOIN GRCharacteristics ON GRGrantCharacteristics.CharacteristicId = GRCharacteristics.CharacteristicId
INNER JOIN (SELECT YEAR(V_GRHist1.DatePaid) AS Year, MAX(V_GRHistory.AdjustedAmount)  AS PayAmount
            FROM        
            V_GRHist1  
            INNER JOIN GRGrantCharacteristics
            INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId
            GROUP BY YEAR(V_GRHist1.DatePaid)
)a  ON a.PayAmount = V_GRHistory.AdjustedAmount and a.Year = YEAR(V_GRHist1.DatePaid)
0
 
TinaSCAuthor Commented:
I ran it & received a timeout error (see attached).

ee-error-122309.doc
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
can you run it on a new query window, instead of a query designer
0
 
TinaSCAuthor Commented:
Thanks - one last question before I close.  Can you explain why it would work in a new query window and not in query designer?
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
some of the features are not supported in the Query designer, also it used to hold the locks for a long time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.