Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 19:51:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hey Megan:

In my first reply to you, I didn't think about it right away because your data did not include any examples of the same customer with the same amount (a tie). If this should occur and be the largest value, you could have two rows with a RANK of 1 and no rows with a rank of 2 (see data below where I added two rows for customer 1000082). (If you never have duplicate amounts, ignore this post.)

     customer   act_date     amount
     1000020   03/09/2008     1.331
     1000020   03/15/2008    93.109
     1000020   03/30/2008   368.622
     1000082   03/17/2008    38.322
     1000082   03/18/2008    38.322
     1000082   03/19/2008    28.322
     1000098   03/01/2008    14.114
     1000098   03/15/2008     2.396
     1000098   03/15/2008    13.802

If this is possible, you need the following SELECT in a view instead of my first pass at this:

     replace view rank_cnt as
     sel *
     from (sel distinct customer, amount from customer) dt
     qualify rank() over (partition by customer order by amount desc) =
         case when count(*) over (partition by customer) > 1 then 2 else 1 end

I am using DISTINCT because I anticipate very few duplicates. However, if you know that your data has a lot of dups, use GROUP BY instead because it will be more efficient.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>