# max entry from a list of entries based on their rank

### Question

• Hi

I have an column like

payemntdate firstNAme amount

15 jan               abc           50

15 jan               abc           50

16 jan               abc           50

15 jan               xyz           150

16 jan               xyz          1 50

17 jan               xyz           150

now i need firstname where payement date is max.

First i have to give rank to all then pick the max one . that is max payemnt date entry.

output will

16 jan               abc           50

17 jan               xyz           150

kindly help .

Thursday, February 14, 2013 3:57 AM

• Try

with cte as
(select First_name__C,last_name__c,payment_Date__C,Item_Name__c, ROW_NUMBER () Over(partition by First_name__C,last_name__c Order by payment_Date__C desc) rn
from Staging_PayPal__c)
select First_name__C,last_name__c,payment_Date__C,Item_Name__c
from cte
where rn=1;

Many Thanks & Best Regards, Hua Min

• Marked as answer by Thursday, February 14, 2013 4:39 AM
Thursday, February 14, 2013 4:34 AM

### All replies

• Try

with cte as
(select payemntdate, firstNAme, amount, ROW_NUMBER () Over(partition by firstNAme Order by payemntdate desc) rn
from tab1)
select payemntdate, firstNAme, amount
from cte
where rn=1;

Many Thanks & Best Regards, Hua Min

Thursday, February 14, 2013 4:11 AM
• ```Declare @temptable table
(paymentdate varchar(10),firstNAme varchar(10),amount   int)
Insert into @temptable
Select '15 jan','abc',           50
Union all
Select '15 jan','abc',50
Union all
Select '16 jan','abc',50
Union all
Select '15 jan','xyz',150
Union all
Select '16 jan','xyz',150
Union all
Select '17 jan','xyz',150
;with cte as
(
Select ROW_NUMBER() over(partition by firstname order by cast(substring(paymentdate,1,2)+'-'+substring(paymentdate,4,3)+'-'+'2000' as datetime) desc) rownumber,paymentdate,firstname,amount
from @temptable
)
select *from cte where rownumber=1```

Try this added 2000 as a default year

Regards,Eshwar.

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

• Edited by Thursday, February 14, 2013 4:18 AM
Thursday, February 14, 2013 4:15 AM

```declare @ta table ( payemntdate varchar(30),  firstNAme char(16), amount   int)

insert into @ta
select '15 jan',               'abc',           50 union all

select '15 jan',               'abc',           50 union all
select '16 jan',               'abc',           50 union all

select '15 jan',               'xyz',           150 union all

select '16 jan',               'xyz',          150 union all

select '17 jan',               'xyz',           150

select x.payemntdate,x.firstNAme,x.amount from
(
select *,
ROW_NUMBER() over(partition by firstName order by  cast((payemntdate + cast(datepart(year,getdate()) as varchar(20))) as date) desc   ) as seq from @ta
) x where seq=1```

thanks,

Saurabh

Thursday, February 14, 2013 4:18 AM
• i used

with CTE (row_number,First_name__C,last_name__c,payment_Date__C,Item_Name__c)as
(
SELECT ROW_NUMBER () over(partition by Contact__c,Payment_gross__c order by payment_Date__C) as row_number ,
First_name__C,last_name__c,payment_Date__C,Item_Name__c
FROM Staging_PayPal__c
)
select * from CTE
where First_name__C='jose'

it return me ;

1    jose    matos    06:13:49 Jan 24, 2013 PST    Gold Plan
2    jose    matos    11:09:41 Jan 20, 2013 PST    Gold Plan
3    jose    matos    11:38:58 Jan 15, 2013 PST    Gold Plan
4    jose    matos    11:38:58 Jan 15, 2013 PST    Gold Plan

now i need max of it i.e;

4    jose    matos    11:38:58 Jan 15, 2013 PST    Gold Plan

How can i do this?

• Marked as answer by Thursday, February 14, 2013 4:39 AM
• Unmarked as answer by Thursday, February 14, 2013 4:39 AM
Thursday, February 14, 2013 4:22 AM
• Try

with cte as
(select First_name__C,last_name__c,payment_Date__C,Item_Name__c, ROW_NUMBER () Over(partition by First_name__C,last_name__c Order by payment_Date__C desc) rn
from Staging_PayPal__c)
select First_name__C,last_name__c,payment_Date__C,Item_Name__c
from cte
where rn=1;

Many Thanks & Best Regards, Hua Min

• Marked as answer by Thursday, February 14, 2013 4:39 AM
Thursday, February 14, 2013 4:34 AM
• :)

my issue simply solved if i use order by desc.. :)

Many thanks .

Thursday, February 14, 2013 4:38 AM