none
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

Answers

  • 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 PrajapatiNeha 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.



    Thursday, February 14, 2013 4:15 AM
  • 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

    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 PrajapatiNeha Thursday, February 14, 2013 4:39 AM
    • Unmarked as answer by PrajapatiNeha 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 PrajapatiNeha 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