Created
July 2, 2014 06:14
-
-
Save nonsleepr/afd37de1622d935995eb to your computer and use it in GitHub Desktop.
DENSE_RANK for Teradata
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Via http://forums.teradata.com/forum/database/dense-rank | |
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment