How to not to skip a RANK ?

Analytics
Enthusiast

How to not to skip a RANK ?

Hi,

I have a table with store #, item # and the sales with billions of records. I need to rank by the sales to find out the top 100 selling items. I can use the RANK function and find it out. However, the issue is, user wants top 100 items without skipping a rank.

For example, if item A and B both sale $100 then teradata would give same rank to them, say rank 5. Say item C does sale of $99.99, it would be rank 7. There won't be rank 6 as there are 2 entries with rank 5. They don't want that. They want item C to be rank 6.

How do I achieve that ? Is there any function in teradata that does it ?

Thanks.

7 REPLIES
Enthusiast

Re: How to not to skip a RANK ?

Use ROW_NUMBER()

It has the same syntax as RANK but assigns incremental values in case of same rank value...

Enthusiast

Re: How to not to skip a RANK ?

I don't think that will work because users want top 100 rank values. But for them ranking means not like teradata ranking. They don't want to skip a rank. With row_number, I will get unique number count, but how to identify what are top 100.

For example, as per RANK and ROW_NUMBER we will get output like below -






Store Item Sales Rank  Row Number
1 A 100 1 1
1 B 100 1 2
1 C 99.99 3 3
1 D 99.98 4 4
1 E 90 5 5
1 F 90 5 6
1 G 85 7 7
1 . . . .
1 . . . .
1 . . . .
1 XXX 50 100 100

But they want like -




Store Item Sales Rank 
1 A 100 1
1 B 100 1
1 C 99.99 2
1 D 99.98 3
1 E 90 4
1 F 90 4
1 G 85 5
1 . . .
1 . . .
1 . . .
1 XXX 50 80
1 . . .
1 . . .
1 . . .
1 ZZZ 20 100

So not sure, how do I go till item = zzz (and not stop at xxx) to get the required results.

Enthusiast

Re: How to not to skip a RANK ?

If I understand correctly, you just want top 100 values according to the sales so you can put a limit using QUALIFY clause...

SELECT STORE, ITEM, SALES, ROW_NUMBER() OVER(PARTITION BY SALES ORDER BY SALES DESC) RN
FROM <<TABLE>>
QUALIFY RN <= 100
Highlighted
Junior Supporter

Re: How to not to skip a RANK ?

Hi.

You could try something like:

SELECT a.Store_Item,

a.Sales,

b.The_rank

FROM YOUR_TABLE a,

( SELECT c.Sales ,

row_number() OVER (ORDER BY c.Sales DESC) The_Rank

FROM ( SELECT DISTINCT Sales FROM YOUR_TABLE ) c

QUALIFY The_Rank <= 100

) b

WHERE a.Sales = b.Sales

ORDER BY b.The_Rank;

HTH.

Cheers.

Carlos.

Enthusiast

Re: How to not to skip a RANK ?

First, thank you both for your help. The solution provided by Qaisar would fell short a bit, but the solution provided by Carlos would work perfectly. Thanks again guys.

Junior Contributor

Re: How to not to skip a RANK ?

In TD13.10 there's an example "windowed aggregate UDF" in C and it's a DENSE_RANK, which is what you're looking for.

You might also read http://developer.teradata.com/node/8048 for different solutions.

Of course it's a huge overhead to run OLAP functions on billions of rows, you should add a quite selective WHERE condition to filter low values.

Dieter

Enthusiast

Re: How to not to skip a RANK ?

That's good. Something new to learn. Thank you.