All Forums Analytics
KS42982 137 posts Joined 12/12
05 Feb 2013
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.

Qaisar Kiani 337 posts Joined 11/05
05 Feb 2013

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

KS42982 137 posts Joined 12/12
05 Feb 2013

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.
 

Qaisar Kiani 337 posts Joined 11/05
06 Feb 2013

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

 

CarlosAL 512 posts Joined 04/08
06 Feb 2013

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.
 

KS42982 137 posts Joined 12/12
06 Feb 2013

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.

dnoeth 4628 posts Joined 11/04
09 Feb 2013

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

Dieter

KS42982 137 posts Joined 12/12
11 Feb 2013

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

You must sign in to leave a comment.