script problem selecting max(amount)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

script problem selecting max(amount)

Author

Topic

wlaker1207
Starting Member

1 Post

Posted - 2009-01-14 : 10:47:38

I need to create a view of my database that selects the largest gift donated by a donor and the date of that gift. The script below gives me the information I need but if a donor has consistently donated the same amount, it will return the first instance of the highest amount.

RESULT IS 10237 06/30/05 500.00
I want 10237 08/26/08 500.00

Create view MX_Largest_Gift_View
as
select g.giftid
, gifteffdat as largestgiftdate
, gifttype as largestgifttype
, giftamount as largestgiftamount
, giftamtplg as largestgiftamtplg
, giftacctnm as largestgiftacctnm
from gifts_full as g
where gifttype in ('g','y','b','c','p')and giftamount=(select max(giftamount)
from gifts g1
where g1.giftid = g. giftid)

giftid largestgiftdate largestgiftamount
10237 08/26/08 500.00
10237 09/04/07 500.00
10237 09/01/06 500.00
10237 12/29/08 200.00
10237 06/30/05 500.00
10237 06/23/04 250.00
10237 06/30/04 25.00

Thanks in advance for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 10:56:32

You need the IN clause in the correlated subquery too, or use the SQL Server 2005 variant

CREATE VIEW MX_Largest_Gift_View
AS

SELECT	GiftID,
	GiftEffDat AS largestGiftDate,
	GiftType AS largestGiftType,
	GiftAmount AS largestGiftAmount,
	GiftAmtPlg AS largestGiftAmtPlg,
	GiftAcctNum AS largestGiftAcctNum
FROM	(
		SELECT	GiftID,
			GiftEffDat,
			GiftType,
			GiftAmount,
			GiftAmtPlg,
			GiftAcctNum,
			ROW_NUMBER() OVER (PARTITION BY GiftID ORDER BY GiftAmount DESC) AS recID
		FROM	Gifts
		WHERE	GiftType IN ('g', 'y', 'b', 'c', 'p')
	) AS d
WHERE	recID = 1

Create view MX_Largest_Gift_View
as
select g.giftid 
, gifteffdat as largestgiftdate
, gifttype as largestgifttype
, giftamount as largestgiftamount
, giftamtplg as largestgiftamtplg
, giftacctnm as largestgiftacctnm 
from gifts_full as g
where gifttype in ('g','y','b','c','p')and giftamount=(select max(giftamount)
from gifts g1 
where g1.giftid = g. giftid and gifttype in ('g','y','b','c','p'))

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources