Order of RANK(yy)

Database
Enthusiast

Order of RANK(yy)

I am a little confused about the suntax of the RANK() function.

I thought that the proper syntax was: RANK() OVER (PARTITION BY xx ORDER BY yy)

But I found that you can write: RANK(YY).

But RANK(yy) and RANK() OVER (ORDER BY yy) order opposite. If I have input yy = 1,2,2,3 then RANK(yy) gives 1=4,2=2 and 3=1, but RANK() OVER (ORDER BY yy) gives 1=1, 2=2 and 3=4.

RANK(yy) order DESC as default, but RANK() OVER (ORDER BY yy) order ASC as default. To have RANK(yy) order ASC I have to write RANK(yy ASC). I do not understand why the RANK(yy) has opposite default than the ORDER by version?

Peter Schwennesen

Tags (1)
3 REPLIES
Junior Contributor

Re: Order of RANK(yy)

Hi Peter,

don't use it, this is deprecated syntax.

Teradata implemented some OLAP functions like RANK, MAVG, CSUM in V2R3 before they existed in Standard SQL, but added the ANSI versions in V2R4.

And for ranking you usually want the highest value to be #1, so they decided to use DESC as default :-)

Dieter 

Enthusiast

Re: Order of RANK(yy)

ok so the best is to use the OVER (ORDER BY version?

Junior Contributor

Re: Order of RANK(yy)

Yep, of course, that's what i ment when i wrote "don't use it" ;-)

Dieter