All Forums Database
TdMan 91 posts Joined 01/07
02 Nov 2007

What is DENSE_RANK? How to implement it (syntax).

02 Nov 2007

Dense Rank doesnt leave gaps between ranks.For example in normal Ranking operation if two people have same marks, they both would be given the same Rank, say 1.Now the next person would be given Rank 3 in regular ranking operation.In Dense Rank this person would be given Rank 2.Once ranked in a particular order through Rank function, we can then rank the rows with generator or something if you want.Maybe somone else can throw light on how to attain a Dense Ranking directly :)Regards,Annal T

dnoeth 4628 posts Joined 11/04
02 Nov 2007

DENSE_RANK calculates the number of proceeding distinct values, plus one. If multiple rows haveequal values, they all get the same rank, but there are no gaps. Each change in data value causes theranking number to be incremented by one. It's not implemented in Teradata (probably because ofperformance reasons), but can be rewritten:SELECT department_number, /*** Syntax not implemented in Teradata ***/ DENSE_RANK() OVER (PARTITION BY department_number ORDER BY salary_amount), last_name, salary_amountFROM employee;Same result using Teradata SQL:SELECT e.department_number, dt.rnk AS "DENSE_RANK", e.last_name, e.salary_amountFROM cs_views.employee eJOIN ( SELECT salary_amount, RANK() OVER (ORDER BY salary_amount) AS rnk FROM cs_views.employee GROUP BY 1 ) dtON e.salary_amount = dt.salary_amountORDER BY rnk, e.salary_amount;Probably more efficient:SELECT salary_amount, rnk, SUM(x) OVER (ORDER BY salary_amount, x DESC ROWS UNBOUNDED PRECEDING) AS "DENSE_RANK"FROM ( SELECT salary_amount, RANK() OVER (ORDER BY salary_amount) AS rnk, CASE WHEN salary_amount = MIN(salary_amount ) OVER (ORDER BY salary_amount ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS x FROM employee ) dt;SELECT salary_amount, SUM(x) OVER (ORDER BY salary_amount, x DESC ROWS UNBOUNDED PRECEDING) AS "DENSE_RANK"FROM ( SELECT salary_amount, CASE WHEN ROW_NUMBER() OVER (PARTITION BY salary_amount ORDER BY salary_amount) = 1 THEN 1 ELSE 0 END AS x FROM employee ) dt;Dieter


02 Nov 2007

I'm not sure if the first option would workSELECTe.department_number,dt.rnk AS "DENSE_RANK",e.last_name,e.salary_amountFROM cs_views.employee eJOIN(SELECTsalary_amount,RANK() OVER (ORDER BY salary_amount) AS rnkFROM cs_views.employeeGROUP BY 1) dtON e.salary_amount = dt.salary_amountORDER BY rnk, e.salary_amount;

02 Nov 2007

The other options are really interesting ways to get Dense_Rank :)Regards,Annal T

TdMan 91 posts Joined 01/07
02 Nov 2007

Good to c the posts. Does teradata not have Dense_Rank function?Does any other db have this fn?

02 Nov 2007

As dieter mentioned its not available in Teradata.Dense_Rank is an ANSI function available in other databases like Oracle.Regards,Annal T

sqllion 2 posts Joined 07/10
23 Jul 2010

Dense_Rank returns the rank as positive integers without any gaps in between the ranks. , it will assign the rank to the records as per the condition used in clause. Follow the link to know moreā€¦

jainayush007 50 posts Joined 03/11
29 Apr 2011

This is truely very informative. Since the comments on this post were posted back in 2007. Does Teradata has DENSE_RANK function TD12 and above. Also, I would be glad if someone could guide me what can I refer to know the various options available with ROW_NUMBER and RANK functions. I remain unaware of things like PRECEEDING,FOLLOWING.PROCEEDING..etc..

Thanks in advance.

Ayush Jain

Jim Chapman 449 posts Joined 09/04
04 May 2011

DENSE_RANK is still not available as of release 13.10.

alteves 2 posts Joined 06/12
22 Jul 2014

DENSE_RANK IS available in 14.10

Raja_KT 1246 posts Joined 07/09
25 Jul 2014

In 14.10 it is very much there, you can see
select empid,deptno,sal,dense_rank() over (partition by deptno order by sal) from employee;

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.