The difference between RANK() and DENSE_RANK() is that RANK() leaves gaps in the ranking sequence when there are ties. Let us see this function through an example.
SELECT Empno, Ename, Job, Mgr, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Rank,
DENSE_RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Drank
FROM Emp ORDER BY SAL Desc NULLS LAST;
Compare the difference in the last columns of this output.
Note: To see rows having not null data, we can use the available oracle clause – NULLS FIRST and NULLS LAST which determines the position of rows with NULL values in the ordered query.
If the sequence is in descending order, then NULLS LAST implies that NULL values are smaller than non-NULL ones and rows with NULLs will appear at the bottom of the list. If the NULLS FIRST | NULLS LAST clause is omitted, then NULL values are considered larger than any other values and their ordering position depends on the ASC | DESC arguments.
Using RANK() to Obtain a Top-N Query
Again we are taking the same table example
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank < 6;
To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery. The output is shown below:
Using the same query with a little modification, you can retrieve the bottom-five employees by salary:
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER (ORDER BY SAL ASC NULLS FIRST) AS Emp_Rank
FROM Emp ORDER BY SAL ASC NULLS FIRST)
WHERE Emp_Rank < 6;
The output now will be:
Also, to get better result, here is more help: Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY subclause. Here is the syntax to retrieve the top employee by salary per manager group:
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER (PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST)
AS Emp_Rank
FROM Emp ORDER BY MGR, SAL DESC NULLS LAST)
WHERE Emp_Rank = 1;
The output is:
Related Posts
- SQL TIPS : Writing Multiple Column Subqueries
- SQL Tutorial : Converting Rows to Columns
- Introduction to JOINS in SQL Server
- Encryption and Decryption in SQL Server 2005
- UNIX Command “find” – Tutorial
Tags: DENSE_RANK, NULLS FIRST, NULLS LAST, RANK, RANK and DENSE_RANK, SQL Tutorial
