CODEWITHSUNDEEP
sqlsql-serverselect
Jt2ouan
Jt2ouan

Reputation: 1962

SQL Server select with aggregate functions

I have a table that looks like this(this is just some of the records and their are also more columns too but these are the ones I care about):

nbr    amt     date     
1      10      10/30/2012
1      15      1/30/2012
1      50      11/30/2012
2      10      4/30/2012
2      1000    5/30/2012
2      45      1/15/2012
4      90      12/30/2012
4      89      8/30/2012
3      100     7/30/2012

I'm trying to select the nbr,amt, and date that corresponds to the max(amt) for each nbr using SQL Server 2012.

I have query like this so far which groups it by nbr and selects the max(amt) but it won't let me select date because its not in an aggregate function but if I put it in an aggregate function it selects max(date) which doesn't corrsepond to the actual date that goes with the amt:

,topamt as (
select 
                 nbr
    ,amt
    ,date
,amtrank = row_number() over (partition by ah.member_nbr order by ah.tran_amt desc)

from HISTORY ah 
      amt>=10
and id=6061
and date between '11-01-2012' and '12-31-2012'

so if I change the query to this where am I defining it to grab the max(amt) the results aren't showing the max atleast.

Upvotes: 1

Views: 1091

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

Try using a ranking function:

with TopAmt as
(
  select *
  , amtRank = row_number() over (partition by nbr order by amt desc)
)
select nbr
  , amt
  , date
from TopAmt
where amtRank = 1

Upvotes: 4

Related Questions