All Forums Tools
karan2353 2 posts Joined 06/15
09 Apr 2016
Using Rank Numbers are getting Skipped in Teradata 13.10
Select Region,Store,Sales,
   Rank()over(partition by Region Order by sales desc) as SalesRank 
from SalesTable 
Qualify SalesRank>=4

Input	
Region	Store	Sales	SalesRank
2	821	82224	1
2	615	5323	2
2	143	5323	2
2	242	4123	4

Desired Output	
Region	Store	Sales	SalesRank
2	821	82224	1
2	615	5323	2
2	143	5323	2
2	242	4123	3

My ranking should be in countinous manner e.g. 1,2,3,4,5,....... 
Request you to please suggest how to achieve the desired output.

dnoeth 4628 posts Joined 11/04
10 Apr 2016

TD13.10 is a very old version.
You need a DENSE_RANK, whihc is not supported before TD14.10, but there's a workaround see:
Missing Functions: DENSE_RANK

Dieter

AtardecerR0j0 71 posts Joined 09/12
11 Apr 2016

Try this function instead of Rank()over(...)

row_number()over(partition by region order by sales desc) as SalesRank

 

Be More!!

rakeshp 1 post Joined 04/16
22 Aug 2016

Hello everyone,
I really have big confusion between Rank and Rowno funtions in terradata. Can you please clarify me on these two functions?
Regards
Rakesh
 

You must sign in to leave a comment.