What is the dense rank?
Submitted by: AdministratorThe DENSE_RANK function computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.
The DENSE_RANK function does not skip numbers and will assign the same number to those rows with the same value. Hence, after the result set is built in the inline view, we can simply select all of the rows with a dense rank of three or less, this gives us everyone who makes the top three salaries by department number.
ex:
Give me the set of sales people who make the top 3 salaries - that is, find the set of distinct salary amounts, sort them, take the largest three, and give me everyone who makes one of those values.
SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/
DEPTNO ENAME SAL TOPN
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1 <--- ! (in case of rank JONES will have 3 and dense rank will have 2)
FORD 3000 1 <--- !
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
ALLEN 1600 2
30 TURNER 1500 3
Submitted by: Administrator
The DENSE_RANK function does not skip numbers and will assign the same number to those rows with the same value. Hence, after the result set is built in the inline view, we can simply select all of the rows with a dense rank of three or less, this gives us everyone who makes the top three salaries by department number.
ex:
Give me the set of sales people who make the top 3 salaries - that is, find the set of distinct salary amounts, sort them, take the largest three, and give me everyone who makes one of those values.
SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC
/
DEPTNO ENAME SAL TOPN
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1 <--- ! (in case of rank JONES will have 3 and dense rank will have 2)
FORD 3000 1 <--- !
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
ALLEN 1600 2
30 TURNER 1500 3
Submitted by: Administrator
Read Online Business Objects Job Interview Questions And Answers
Top Business Objects Questions
☺ | What is difference between Webi and Infoview? |
☺ | What is the difference between BoXIR2 and BOXIR3? |
☺ | Explain What is the difference between slice and dice and cross tab report? |
☺ | Explain Are Data mart and DWH normalized or denormalized? Do both of them exist in |
☺ | Explain How can we do load testing for Webi Reports? (Example if I have have to test one webi report for Concurrent users refreshing report at one time)? |
Top Data Warehouse Job Categories
☺ | Warehouse Assistant Interview Questions. |
☺ | Ab Initio Interview Questions. |
☺ | Data Stage Interview Questions. |
☺ | ColdFusion Interview Questions. |
☺ | Data Warehouse Supervisor Interview Questions. |