One of the more interesting aggregation functions is RANK<\/strong>.\u00a0 After doing a standard SELECT<\/strong> and GROUP BY<\/strong>; you can then have SQL rank the results using an aggregated value from another column.<\/p>\n Let’s dive in and look at how RANK<\/strong> works and what can it do for us.<\/p>\n When using the RANK you will need to specify which column you want to RANK on.<\/p>\n Here we will rank the cust_vendor_dim.name by the SUM<\/strong> of the extcost column.\u00a0 In addition the results will be returned in DESC<\/strong> order. DESC<\/strong> means descending order, highest value listed first. We will still need to use the GROUP BY<\/strong> operator to group by cust_vendor_dim.name.<\/p>\n One of the options we can do with RANK<\/strong> is to partition the ranking results.\u00a0 In other words when there is a change in value for a specified column, the ranking will restart.<\/p>\n In this example the query will partition by cust_vendor_dim.state, therefore when the previous rows value in\u00a0cust_vendor_dim.state is different from the current rows value the ranking will restart with 1.\u00a0 The query needs to use the ORDER BY<\/strong> operator to get the correct results.<\/p>\n A variation of RANK<\/strong> is DENSE_RANK<\/strong>, the primary difference is that DENSE_RANK<\/strong> will not skip sequential order of numbers when two rows have the same value.\u00a0 DENSE_RANK<\/strong> will show them as having the same rank and the next row will have the next sequential number.<\/p>\n An example would be DENSE_RANK<\/strong> values of 1,2,3,3,3,4,5,5,6.\u00a0 Where RANK would show the same result as 1,2,3,3,3,6,7,7,9<\/p>\n <\/p>\n One of the most common tasks in grouping is to show a cumulative sum for the column values. with using aggregate SUM<\/strong> with the OVER<\/strong> option you can create a cumulative sum for the specified grouping levels.<\/p>\n 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.<\/p>\n I hope you found the above informative, let me know if you have any questions in the comments below.<\/p>\nRank<\/h5>\n
SELECT cust_vendor_dim.name\r\n ,SUM(EXTCOST) AS sum_extcost\r\n ,RANK() OVER (ORDER BY SUM(EXTCOST) DESC) AS rank_Customer\r\nFROM inventory_fact\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\nGROUP BY cust_vendor_dim.name\r\n;<\/pre>\n
Rank Partition<\/h5>\n
SELECT cust_vendor_dim.state\r\n ,cust_vendor_dim.name\r\n ,SUM(EXTCOST) AS sum_extcost\r\n ,RANK() OVER ( PARTITION BY cust_vendor_dim.state\r\n ORDER BY SUM(EXTCOST) DESC) AS rank_Customer\r\nFROM inventory_fact\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\nGROUP BY cust_vendor_dim.state\r\n ,cust_vendor_dim.name\r\nORDER BY cust_vendor_dim.state\r\n;\r\n<\/pre>\n
<\/h5>\n
Dense_Rank<\/h5>\n
SELECT cust_vendor_dim.name\r\n ,COUNT(inventorykey)\r\n ,RANK() OVER (ORDER BY COUNT(inventorykey) DESC) AS rank_Customer\r\n ,DENSE_RANK() OVER (ORDER BY COUNT(inventorykey) DESC) AS dense_rank_Customer\r\nFROM inventory_fact\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\nGROUP BY cust_vendor_dim.name\r\n;<\/pre>\n
Cumulative Sum<\/strong><\/h5>\n
SELECT zip\r\n ,calyear\r\n ,calmonth\r\n ,SUM(EXTCOST) AS sum_extcost\r\n ,SUM( SUM(EXTCOST)) OVER(ORDER BY zip,calyear,calmonth\r\n ROWS UNBOUNDED PRECEDING\r\n ) AS cumulative_extcost\r\nFROM inventory_fact\r\n INNER JOIN date_dim\r\n ON inventory_fact.datekey = date_dim.datekey\r\n INNER JOIN cust_vendor_dim\r\n ON inventory_fact.custvendorkey = cust_vendor_dim.custvendorkey\r\nWHERE transtypekey = 5 -- 'Sales Order Shipment'\r\nGROUP BY zip\r\n ,calyear\r\n ,calmonth\r\n;<\/pre>\n