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 .

Answer 1

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

Answer 2

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.

Answer 3

Follow below example....

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

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 4

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?

Answer 5

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

Answer 6

:)

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

Many thanks .