Troubleshooting max formula

[sap1958]

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv in ('5','15')
and giftid = '0000054529'
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

I ran the query and got an error based on the giftID not contained in either aggregate or group by function. I added group by giftid and still get the error. I am trying to get the top or largest gift and the date associated with it

 

[gmmastros]

This part:

[tt][blue]
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv in ('5','15')
and giftid = '0000054529'
) a
[/blue][/tt]

is considered a derived table. The derived table part MUST be able to be executed on it's own without generating any errors. Unfortunately, it doesn't because you are returning a column without an aggregate (giftid) and it's also not in the group by clause. In your case, I think you want the max(giftjntamt) for each giftid, so you should add a group by to the derived table part, like this:

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select giftid, Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv  in ('5','15')
and giftid = '0000054529'
[!]Group By giftid[/!]
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

Looking closer, I see that you are hard coding the giftid. You can include a constant in the select clause without needing an aggregate or grouping, so this would also work.

select a.giftid,a.MaxOfgiftjntamt,b.gifteffdat
from
(
select [!]'0000054529' As giftid[/!], Max(giftjntamt) AS MaxOfgiftjntamt
FROM gifts_VIEW
WHERE gifteffdat > '20050701'
AND giftacctdv  in ('5','15')
and giftid = '0000054529'
) a
inner join gifts_VIEW b on a.giftid = b.giftid and a.MaxOfgiftjntamt = b.giftjntamt

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom