TERADATA FAQS AND TERADATA ANALYTICAL FUNCTIONS

syn:

RANK()OVER (PARTITION BY <COLS> ORDER BY <COLS>)

ROW_NUMBER() ....

NOTE:

PARTITION--GROUP

OVER--INDICATION FOR ANALYTICAL OPERATION

OVER-->INDICATE ANALYTICAL FUNCTION



Q: DISPLAY RANK VALUES FOR EVERY CELL VALUE BY KEEPING IN DESCENDING ORDER

DISPLAY RANK FOR EVERY VALUE ACCORDING TO RANK PROTOCOL



SEL PARTYID,PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) FROM PARTY



DISPLAY RANK UNIQUELY FOR EVERY UNIQUE VALUE

SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) FROM PARTY

GROUP BY PARTYINCOME



DISPLAY TOP 3 PARTYINCOME VALUES

SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) R FROM PARTY

QUALIFY R<=3

GROUP BY PARTYINCOME



DISPLAY 3RD MAX PARTYINCOME VALUE

SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) R FROM PARTY

QUALIFY R=3

GROUP BY PARTYINCOME

NOTE: R<=N--FOR TOP N VALUES,R=N FOR N TH MAXIMUM SALARY



DISPLAY LOCATIONWISE TOP 2 PARTYINCOME VALUES

SEL PARTYLOC,PARTYINCOME,RANK() OVER (PARTITION BY PARTYLOC

ORDER BY PARTYINCOME DESC) R FROM PARTY

QUALIFY R<=2

GROUP BY PARTYLOC,PARTYINCOME



DISPLAY UNIQUE VALUE FOR EVERY CELL VALUE STARTING FROM 1

SEL PARTYINCOME,ROW_NUMBER() OVER (ORDER BY PARTYINCOME DESC) R FROM



PARTY



TOP 3,OR 3RD MAX OR LOCATION WISE TOP 2--

IN THE PREVIOUS RANK() FUNCTION REPLACE WITH ROW_NUMBER()



TO WORK WITH BOTTOM VALUES


=============================
USE ORDER BY AND TAKE ASCENDING ORDER