Ranking Window Functions in PL/SQL

This function assigns a specific rank to each row based on some internal criteria.

1. ROW_NUMBER(): Assigns a unique integer id to each row within a partition.
2. RANK(): Assigns a unique integer to each value within a partition. If two rows have the same value for our specified column then they will have the same rank. It will also create gaps when they are tied.
3. DENSE_RANK(): It is similar to rank but it will not create gaps for the ties.

Example of Ranking Window Functions in PL/SQL

In this example, we will compute row number, rank, and dense rank for each row within a partition. We will see the working of the above functions as window functions.

Query:

DECLARE
    CURSOR gfg_deatils IS
        SELECT emp_id, name, department, salary,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary ) AS row_num,
               RANK() OVER (PARTITION BY department ORDER BY salary ) AS rank,
               DENSE_RANK() OVER (PARTITION BY department ORDER BY salary ) AS dense
        FROM w3wiki;
BEGIN
    DBMS_OUTPUT.PUT_LINE('EMP_ID   | Name    | Department  | Salary | Row_Number | Rank   | Dense_Rank ');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
    FOR i IN gfg_deatils LOOP
        DBMS_OUTPUT.PUT_LINE(i.emp_id || '      | ' || i.name || '   |   ' || i.department || '         | ' || i.salary||' |   '||i.row_num||'        |   '||i.rank||'    |   '||i.dense);
    END LOOP;
END;

Output:

Explanation: As explained earlier, we can see that row numbers are unique for each row in a partition. The ranks are the same for the same salaries and it also leaves a gap when two ranks are the same. The dense ranks are also the same for the same salaries but it does not leave a gap unlike in rank. We can see that for id 210 rank is 3 and the dense rank is 2. The query is very similar to the previous one. You can refer to the image for more detailed understanding.

A window function is sometimes referred to as an “Analytic Function“. It is generally used to perform calculations on some specific set of rows related to each row in our result set. There are some real-world applications of the window function, they are sales analysis, inventory management, time series analysis, and so on. In short, the window function provides an analytical insight into the data. We can optimize our operations.

In this article, we are going to discuss about working of “Window Functions in PL/SQL“. We will go through various real-world use cases with some clear and concise examples.