How to correctly use partition function with max function

Question

I have a table as below, I want to extract only one record of every customer having data less than or equal to '06/30/2012' and the largest of the RowNum.

RowNum	customer_ID	date	balance
197	BS:141723	6/30/2012	124693.08
195	BS:165012	5/31/2012	26346.42
27	BS:166289	6/30/2012	5253.67
41	BS:209459	6/30/2012	32673.04
127	BS:141723	6/30/2012	205849.11
15	BS:192907	4/30/2012	106236.71
47	BS:192907	5/31/2012	7430.6
97	BS:165012	4/30/2012	721

The expected output should be:

customer_ID	balance	RowNum
BS:141723	124693.08	197
BS:165012	26346.42	195
BS:166289	5253.67	27
BS:192907	7430.6	47
BS:209459	32673.04	41

I have written this query

select DISTINCT customer_ID, balance
    , MAX(RowNum) over (PARTITION by customer_ID)
from Test

and the result which I am getting is below which is incorrect.

customer_ID	balance	RowNum
BS:141723	124693.08	197
BS:141723	205849.11	197
BS:165012	721	195
BS:165012	26346.42	195
BS:166289	5253.67	27
BS:192907	7430.6	47
BS:192907	106236.71	47
BS:209459	32673.04	41

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). If not, why does it not? — Thom A, May 10 '21 at 08:33
I am going through that as there are lot f answers for that, — Tarun Singh, May 10 '21 at 08:47
@TarunSingh you can check the accepted answer first (answer with the green tick). — sacse, May 10 '21 at 08:51

Brucelin Michael · Accepted Answer · 2021-05-10T08:50:52.597

you can do it like this:

create table #temp(RowNum int, customer_ID varchar(32), [date] date, balance decimal(18,2))
insert into #temp(RowNum, customer_ID, [date], balance)
values   (197, 'BS:141723', '6/30/2012', 124693.08)
       , (195, 'BS:165012', '5/31/2012', 26346.42)
       , (27,  'BS:166289', '6/30/2012', 5253.67)
       , (41,  'BS:209459', '6/30/2012', 32673.04)
       , (127, 'BS:141723', '6/30/2012', 205849.11)
       , (15,  'BS:192907', '4/30/2012', 106236.71)
       , (47,  'BS:192907', '5/31/2012', 7430.6)
       , (97,  'BS:165012', '4/30/2012', 721)


select * from #temp

;with c0 as(
select customer_ID, balance, RowNum
       , ROW_NUMBER() over(partition by customer_ID order by [date] desc) as rid
from #temp
where [date] <= CONVERT(date, '06/30/2012')
)
select customer_ID, balance, RowNum
from c0 where rid = 1

--OR

select customer_ID, balance, RowNum
from (select customer_ID, balance, RowNum
             , ROW_NUMBER() over(partition by customer_ID order by [date] desc) as rid
      from #temp
      where [date] <= CONVERT(date, '06/30/2012')
) as dt
where rid = 1

```select customer_ID, balance, RowNum from (select customer_ID, balance, RowNum , ROW_NUMBER() over(partition by customer_ID order by [date] desc) as rid from #temp where [date] <= CONVERT(date, '06/30/2012') ) as dt where rid = 1``` — Brucelin Michael, May 10 '21 at 08:49

How to correctly use partition function with max function

1 Answers1