DENSE_RANK

Database
Enthusiast

DENSE_RANK

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

Re: DENSE_RANK

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
Junior Contributor

Re: DENSE_RANK

DENSE_RANK calculates the number of proceeding distinct values, plus one. If multiple rows have
equal values, they all get the same rank, but there are no gaps. Each change in data value causes the
ranking number to be incremented by one. It's not implemented in Teradata (probably because of
performance 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_amount
FROM employee;

Same result using Teradata SQL:

SELECT
e.department_number,
dt.rnk AS "DENSE_RANK",
e.last_name,
e.salary_amount
FROM cs_views.employee e
JOIN
(
SELECT
salary_amount,
RANK() OVER (ORDER BY salary_amount) AS rnk
FROM cs_views.employee
GROUP BY 1
) dt
ON e.salary_amount = dt.salary_amount
ORDER 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
Enthusiast

Re: DENSE_RANK

I'm not sure if the first option would work

SELECT
e.department_number,
dt.rnk AS "DENSE_RANK",
e.last_name,
e.salary_amount
FROM cs_views.employee e
JOIN
(
SELECT
salary_amount,
RANK() OVER (ORDER BY salary_amount) AS rnk
FROM cs_views.employee
GROUP BY 1
) dt
ON e.salary_amount = dt.salary_amount
ORDER BY rnk, e.salary_amount;
Enthusiast

Re: DENSE_RANK

The other options are really interesting ways to get Dense_Rank :)

Regards,
Annal T
Enthusiast

Re: DENSE_RANK

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

Re: DENSE_RANK

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

Regards,
Annal T

Re: DENSE_RANK

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…
http://www.sqllion.com/2010/07/dense_rank/
Enthusiast

Re: DENSE_RANK

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
Enthusiast

Re: DENSE_RANK

DENSE_RANK is still not available as of release 13.10.