Oracle PL/SQL Aggregation  – Rank

One of the more interesting aggregation functions is RANK.  After doing a standard SELECT and GROUP BY; you can then have SQL rank the results using an aggregated value from another column.

Let’s dive in and look at how RANK works and what can it do for us.

Rank

When using the RANK you will need to specify which column you want to RANK on.

Here we will rank the cust_vendor_dim.name by the SUM of the extcost column.  In addition the results will be returned in DESC order. DESC means descending order, highest value listed first. We will still need to use the GROUP BY operator to group by cust_vendor_dim.name.

Rank Partition

One of the options we can do with RANK is to partition the ranking results.  In other words when there is a change in value for a specified column, the ranking will restart.

In this example the query will partition by cust_vendor_dim.state, therefore when the previous rows value in cust_vendor_dim.state is different from the current rows value the ranking will restart with 1.  The query needs to use the ORDER BY operator to get the correct results.

Dense_Rank

A variation of RANK is DENSE_RANK, the primary difference is that DENSE_RANK will not skip sequential order of numbers when two rows have the same value.  DENSE_RANK will show them as having the same rank and the next row will have the next sequential number.

An example would be DENSE_RANK values of 1,2,3,3,3,4,5,5,6.  Where RANK would show the same result as 1,2,3,3,3,6,7,7,9

 

Cumulative Sum

One of the most common tasks in grouping is to show a cumulative sum for the column values. with using aggregate SUM with the OVER option you can create a cumulative sum for the specified grouping levels.

In the example above query will produce a cumulative sum for each group of zip, calyear, calmonth. The ROWS option specifies to look at all preceding rows.

I hope you found the above informative, let me know if you have any questions in the comments below.

— michael.data@eipsoftware.com