Rank duplicates 1

[jhall01]

I have a recordset that comes back with duplicate customers because of purchase date. Since the dates vary i need to translate the duplicates out into columns. I want to rank the duplicates by date and only take 5 duplicates?

Normally in teradata i would use a CSUM() or RANK() function to create a derived table to translate out that keeps track of the number of duplicates. Is there a way to do this in SQL Server 2000?

i.e.

customer date prod rank
cust1 1/1/2000 prod1 1
cust1 4/4/2004 prod2 2
cust1 6/5/2004 prod8 3

I can take the above table and translate out using case statements, but i don't know how to generate the rank column

Thanks,

Jon

 

[nigelrivett]

select customer, date, prod, rank = (select count(*) from tbl t2 where t2.customer = t1.customer and t2.date <= t1.date)
from tbl t1


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[ESquared]

What nigelrivett gave you is the 'cleanest' code for this sort of thing I know of. Just be aware that for gigantic tables it could function very poorly and you might want to consider an alternate version that uses variables and an UPDATE statement.

 

[jhall01]

Yeah, I it was only a few hundred records so it wasn't a big deal. I also took this idea and modified it a bit to mechanize the process in a permanent table.

Thanks for you quick responses!

Jon