Grouping Issue in SQL

[sap1958]

select max(giftjntamt)as Amt,gifteffdat
from gifts with(nolock)
where gifteffdat >='7/1/1984'
and giftacctdv = '11'
and gifttype in ('c','g','y')
and giftid = '0ZU9531059'
group by giftid,gifteffdat

sample data
1000.00 1996-01-10 00:00:00.000
1500.00 1996-12-19 00:00:00.000
1500.00 1999-03-09 00:00:00.000
1500.00 2001-02-06 00:00:00.000
500.00 2002-12-26 00:00:00.000

desired output is the largest number and the accompanying date. Instead I get 5 separate amounts. $1500 is the largest amount.

Desired data
1500.00 2001-02-06 00:00:00.000

 

[markros]

select top (1)
giftjntamt as Amt,gifteffdat
from gifts with(nolock)
where  gifteffdat >='7/1/1984'
and  giftacctdv = '11'
and  gifttype in ('c','g','y')
and giftid = '0ZU9531059' 
order by GiftJntAmt DESC

PluralSight Learning Library

 

[r937]

if $1500 is the largest number, then the query must return 3 rows, not 1

1500.00 1996-12-19 00:00:00.000
1500.00 1999-03-09 00:00:00.000
1500.00 2001-02-06 00:00:00.000

the solution by markros does not do it

try this --

SELECT gifteffdat
     , giftjntamt as max_amt  
  FROM gifts WITH(NOLOCK)
 WHERE gifteffdat >= '7/1/1984'
   AND giftacctdv = '11'
   AND gifttype IN ('c','g','y')
   AND giftid = '0ZU9531059' 
   AND giftjntamt =
       ( SELECT MAX(giftjntamt)     
           FROM gifts WITH(NOLOCK)  
          WHERE gifteffdat >= '7/1/1984'
            AND giftacctdv = '11'
            AND gifttype IN ('c','g','y')
            AND giftid = '0ZU9531059' )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

r937. If we want to return all 3 top first items, add with ties, e.g.

select top (1) with ties
giftjntamt as Amt,gifteffdat
from gifts with(nolock)
where  gifteffdat >='7/1/1984'
and  giftacctdv = '11'
and  gifttype in ('c','g','y')
and giftid = '0ZU9531059' 
order by GiftJntAmt DESC

PluralSight Learning Library