Oracle/SQL Server – RANK, DENSE_RANK and ROW_NUMBER Analytic Functions – Basic Examples

Note: The illustrations on this post are done with Oracle. However, by making minor changes, you should be able to run it on SQL Server and the concepts remain the same. Please refer this post for changes needed.

RANK, DENSE_RANK and ROW_NUMBER are three powerful analytic functions that every database developer should be thoroughly familiar with (but few do or use).  Just these three analytic functions eliminate the need for a lot of complex grouping functions. They are extraordinary in their power and simplicity.

Typically, analytic functions take the following syntax

row_number

* *Thanks for the picture Oracle

In this case ROW_NUMBER is the analytic function. The OVER clause then further divides the data into groups if the PARTITION BY clause is specified. Once the data is divided using the PARTITION BY clause which can specify more than one column, the data can be logically ordered within each group (AKA partition) using the ORDER BY clause. The ORDER BY clause may also have multiple columns specified with ASC and/or DESC. The right analytic function can then be used the get the associated value from each row.

  • ROW_NUMBER is just a running sequence value within each ordered group.
  • RANK is very similar to ROW_NUMBER but one or more values that are equal within a group get the same rank.
  • DENSE_RANK is the same as RANK but it does not skip numbers when earlier rows got the same rank for equal values

Create a table with dummy data:

CREATE TABLE Emp
AS
SELECT * FROM
(
    SELECT 'Employee 1' AS Employee, TO_CHAR(NULL) AS Manager, 10000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.1' AS Employee, 'Employee 1' AS Manager, 9000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2' AS Employee, 'Employee 1' AS Manager, 9000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.3' AS Employee, 'Employee 1' AS Manager, 8000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1' AS Employee, 'Employee 1.2' AS Manager, 7000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.2' AS Employee, 'Employee 1.2' AS Manager, 6000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1.1' AS Employee, 'Employee 1.2.1' AS Manager, 5000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1.2' AS Employee, 'Employee 1.2.1' AS Manager, 4000 AS Salary FROM DUAL
    UNION ALL
    SELECT 'Employee 1.2.1.3' AS Employee, 'Employee 1.2.1' AS Manager, 4000 AS Salary FROM DUAL
);

This is the data:

SELECT *
FROM Emp;
EMPLOYEE MANAGER SALARY
Employee 1 10000
Employee 1.1 Employee 1 9000
Employee 1.2 Employee 1 9000
Employee 1.3 Employee 1 8000
Employee 1.2.1 Employee 1.2 7000
Employee 1.2.2 Employee 1.2 6000
Employee 1.2.1.1 Employee 1.2.1 5000
Employee 1.2.1.2 Employee 1.2.1 4000
Employee 1.2.1.3 Employee 1.2.1 4000

Really basic usage example

Here are a couple of examples queries that use the above data to illustrate RANK, DENSE_RANK and ROW_NUMBER functions.

SELECT
    manager,
    employee,
    salary,
    -------- Rank salary ascending within each manager --------
    RANK() OVER (PARTITION BY manager ORDER BY salary) rnk_sal_within_mgr,
    -------- Dense rank salary ascending within each manager --------
    DENSE_RANK() OVER (PARTITION BY manager ORDER BY salary) drnk_sal_within_mgr,
    -------- Get the ranking by salary within each manager without duplicating ranking --------
    ROW_NUMBER() OVER (PARTITION BY manager ORDER BY salary) rn_within_mgr,
    -------- Rank salary descending within each manager --------
    RANK() OVER (PARTITION BY manager ORDER BY salary DESC) rnk_sal_desc_within_mgr,
    -------- Dense rank salary DEscending within each manager --------
    DENSE_RANK() OVER (PARTITION BY manager ORDER BY salary DESC) drnk_sal_desc_within_mgr,
FROM
    Emp e;

The column name is indicative of what is being selected (plus read code comments above)

MANAGER EMPLOYEE SALARY RNK_SAL_WITHIN_MGR DRNK_SAL_WITHIN_MGR RN_WITHIN_MGR RNK_SAL_DESC_WITHIN_MGR DRNK_SAL_DESC_WITHIN_MGR
Employee 1.2.1 Employee 1.2.1.2 4000 1 1 1 2 2
Employee 1.2.1 Employee 1.2.1.3 4000 1 1 2 2 2
Employee 1.2.1 Employee 1.2.1.1 5000 3 2 3 1 1
Employee 1.2 Employee 1.2.2 6000 1 1 1 2 2
Employee 1.2 Employee 1.2.1 7000 2 2 2 1 1
Employee 1 Employee 1.3 8000 1 1 1 3 2
Employee 1 Employee 1.1 9000 2 2 2 1 1
Employee 1 Employee 1.2 9000 2 2 3 1 1
Employee 1 10000 1 1 1 1 1

MIN/MAX without grouping? YES

SELECT
    manager,
    employee,
    salary,
    -------- Rank salary ascending across the board --------
    RANK() OVER (ORDER BY salary) rnk_sal_all,
    -------- Dense rank salary ascending across the board --------
    DENSE_RANK() OVER (ORDER BY salary) drnk_sal_all,
    -------- Get the ranking by salary ascending across the board without duplicating ranking --------
    ROW_NUMBER() OVER (ORDER BY salary) rn_all,
    -------- Get the employee with least salary within each manager --------
    MIN(salary) OVER (PARTITION BY manager) min_sal_within_mgr,
    -------- Get the employee with most salary within each manager --------
    MAX(salary) OVER (PARTITION BY manager) max_sal_within_mgr
FROM
    Emp e;

The column name is indicative of what is being selected (plus read code comments above)

MANAGER EMPLOYEE SALARY RNK_SAL_ALL DRNK_SAL_ALL RN_ALL MIN_SAL_WITHIN_MGR MAX_SAL_WITHIN_MGR
Employee 1.2.1 Employee 1.2.1.2 4000 1 1 1 4000 5000
Employee 1.2.1 Employee 1.2.1.3 4000 1 1 2 4000 5000
Employee 1.2.1 Employee 1.2.1.1 5000 3 2 3 4000 5000
Employee 1.2 Employee 1.2.2 6000 4 3 4 6000 7000
Employee 1.2 Employee 1.2.1 7000 5 4 5 6000 7000
Employee 1 Employee 1.3 8000 6 5 6 8000 9000
Employee 1 Employee 1.1 9000 7 6 7 8000 9000
Employee 1 Employee 1.2 9000 7 6 8 8000 9000
Employee 1 10000 9 7 9 10000 10000

Did anyone notice that we used MIN and MAX without even using a “GROUP BY” above? In the above case the MIN and MAX and working with the grouping that PARTITION BY dictates.

There are a lot of additional analytic functions. Just google “Oracle analytic functions” to explore further.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s