Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
12:20 AM

11-02-2007
12:20 AM

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

11 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
04:21 AM

11-02-2007
04:21 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
04:56 AM

11-02-2007
04:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
05:31 AM

11-02-2007
05:31 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
05:35 AM

11-02-2007
05:35 AM

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

Regards,

Annal T

Regards,

Annal T

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
09:42 AM

11-02-2007
09:42 AM

Good to c the posts. Does teradata not have Dense_Rank function?

Does any other db have this fn?

Does any other db have this fn?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2007
10:52 AM

11-02-2007
10:52 AM

As dieter mentioned its not available in Teradata.

Dense_Rank is an ANSI function available in other databases like Oracle.

Regards,

Annal T

Dense_Rank is an ANSI function available in other databases like Oracle.

Regards,

Annal T

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-23-2010
03:00 AM

07-23-2010
03:00 AM

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/

http://www.sqllion.com/2010/07/dense_rank/

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-29-2011
11:56 AM

04-29-2011
11:56 AM

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

Thanks in advance.

Ayush Jain

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-04-2011
11:14 AM

05-04-2011
11:14 AM

DENSE_RANK is still not available as of release 13.10.