SQL DENSE RANK Function

Only when used in conjunction with a SELECT list of query are RANK() and DENSE RANK() permitted. In other words, although being logical SQL expressions, these two functions cannot be utilised as criteria in Saved Searches or in the Results tab when paired with a Summary Type.

To give an example, suppose that a user wants to see the most recent four bills for each of their vendors. The following formula should be used in the Criteria tab as an outright solution:

Numerical expression (Numeric) = 
CASE WHEN (DENSE RANK() OVER (PARTITION BY name ORDER BY date DESC) 4) THEN 1 ELSE 0 END = 1

According to this algorithm, the system will display the first 4 bills out of a list of bills by date that are ranked by vendor in descending order.

However, due to the previously mentioned reason, the system will produce an unexpected error. The alternative method is to create the Saved Search in the following way:

1. Navigate to Transactions > Management > Saved Searches > New
2. Select Transaction
3. On the Criteria tab add Type is Bill and Main Line is Yes
4. On the Results tab add the following:
• Formula(Date) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {trandate} END
• Formula(Text) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {mainname} END
• Formula(Currency) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {amount} END
5. Hit Pivot Report
6. Drag Vendor in the Row Dimensions, Date in the Columns and Amount in the Data
7. Hit Run Report
8. When the Pivot Report shows up, search for the Vendor that says "Empty" and hit the Collapse button

The first four Bills' details will only be seen via this Saved Search / Pivot Report, which hides/masks the remaining Bills. Keep in mind that this Saved Search will continue to look for all Bills in the database and could cause runtime problems, especially if there are numerous transactions involved. Setting criteria like date as a consideration in this situation.

Example 1: Specify many conditions to order by in your analytic function:

SELECT *
  FROM (SELECT id,
   col1,
   col2,
   col3,
  dense_rank() over (partition by id
  order by (case when col1 = 'xyz'                                                    	
  then 1 
  else 0 
  end) desc,
  col2 asc, col3 asc) rnk
FROM your_table) WHERE rnk = 1

Given that you used the dense_rank tag, I'm assuming you want dense_rank. It's unclear from the query itself if you want to utilise the rank, dense_rank, or row number analytic functions because you don't discuss how you want to treat ties or whether ties are even possible. If there are ties for first place, rank and dense_rank will act equally and deliver many rows if you only ever obtain the highest ranking row for each id. By arbitrarily breaking the tie, row number will always yield one row. You must consider ties and expect different behaviour from rank and dense rank if you want to obtain data other than the top row per id. If two rows are tied for first, dense_rank will assign the third row a rnk of 2 while rank will assign it a rnk of 3.

Example 2: The below code is for MS SQL Server. Thanks to @Victor Hugo Terceros, for sample code.

Step 1:

DECLARE @tbl TABLE
  (
     age INT,
     grp VARCHAR(20)
  )

Step 2:

INSERT INTO @tbl
SELECT 1,
       'A'
UNION
SELECT 12,
       'A'
UNION
SELECT 20,
       'A'
UNION
SELECT 19,
       'B'
UNION
SELECT 30,
       'B'
UNION
SELECT 11,
       'B'
UNION
SELECT 4,
       'C'
UNION
SELECT 14,
       'C'
UNION
SELECT 5,
       'B'
UNION
SELECT 16,
       'D'

Step 3:

SELECT grp AS Policy,
  age,
  under21 AS Under21,
  CASE
  WHEN under21 = 0 THEN Dense_rank()
     OVER(
     partition BY grp
     ORDER BY under21age DESC)
 ELSE 0
 END AS Rank_Under_21
FROM  (SELECT CASE
 WHEN age < 21 THEN 0
 ELSE 1
 END AS Under21,
 CASE
 WHEN age < 21 THEN age
 ELSE 0
 END AS under21age,
 age,
 grp
FROM   @tbl) AS t 

Example 3: Ranking that occurs with dense_rank but it does not work as expected due to the sorting with the parameter.

For example, if I run the following query:

declare @sort_desc bit = 0

select g.grp_id, g.grp_nm,
m.mbr_id, m.acct_id, m.cst,
row_number() over(order by case when @sort_desc = 0 then g.grp_nm end
    , case when @sort_desc = 0 then m.acct_id end
    , case when @sort_desc = 1 then g.grp_nm end desc
    , case when @sort_desc = 1 then m.acct_id end desc) rn,
dense_rank()  over(order by case when @sort_desc = 0 then g.grp_nm end
    , case when @sort_desc = 0 then m.acct_id end
    , case when @sort_desc = 1 then g.grp_nm end desc
    , case when @sort_desc = 1 then m.acct_id end desc) dr
from grp g
inner join mbr m
on g.grp_id = m.grp_id;

Output:

+--------------------------------------+---------+--------+---------+------+----+----+
|                              GRP_ID  | GRP_NM  | MBR_ID | ACCT_ID |  CST | RN | DR |
+--------------------------------------+---------+--------+---------+------+----+----+
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1  |     10 |       1 |   AA |  1 |  1 |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1  |     11 |       2 |   BB |  2 |  2 |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2  |     12 | 1234578 | blah |  3 |  3 |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3  |     13 |      78 | test |  4 |  4 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4  |     15 |       a | mbr2 |  5 |  5 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4  |     14 |       x | mbr1 |  6 |  6 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5  |     16 |       b | mbr1 |  7 |  7 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5  |     17 |       c | mbr2 |  8 |  8 |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6  |     18 |       a | mbr1 |  9 |  9 |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7  |     19 |       a | mbr1 | 10 | 10 |
+--------------------------------------+---------+--------+---------+------+----+----+

But the desired result is:

+--------------------------------------+---------+--------+---------+------+----+----+
|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | expR|
+--------------------------------------+---------+--------+---------+------+----+----+
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1  |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  1  |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  2  |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  3  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  4  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  4  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  5  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  5  |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  6  |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 |  7  |
+--------------------------------------+---------+--------+---------+------+----+----+

As you can see, the rows are still sorted correctly even if the expR column values are increasing dependent on the grp_id. Any advice would be very appreciated because I am at a lost on how to achieve this effect.

When a group of rows is arranged, DENSE_RANK determines the rank of each row and returns the rank as a NUMBER. The ranks are a series of integers, starting with number 1. The number of distinct values returned by the query has the highest rank value. In the case of ties, rank values are not skipped. The rank of a row is determined by its values for the ranking criteria. Reporting using top-N and bottom-N is facilitated by this function.

Any numeric data type may be passed as an input to this method, which returns NUMBER.

Syntax:

DENSE_RANK() OVER ([PARTITION BY COLUMN1] [ORDER BY COLUMN2] 
[ROWS BETWEEN n FOLLOWING|PRECEDING(start window) 
AND m FOLLOWING|PRECEDING|CURRENT ROW)(end window)])

DENSE_RANK computes the dense rank of a hypothetical row identified by the function's inputs with regard to a specified sort specification. It does this as an aggregate function. Because they identify a single row inside each aggregate group, the function's parameters must all evaluate to constant expressions within each aggregate group. Expressions in the order_by_clause of the aggregate and those in constant argument expressions line up in position. Therefore, the number of arguments and the types must both be the same.

The analytical function DENSE_RANK determines the relative importance of each row returned by a query to the other rows using the values of the value_exprs in the order_by_clause.

Example 1: Aalternatively, use DENSE_RANK(), instead! Due to the way DENSE_RANK() applies the ranking, duplicate records will have the same rank. Furthermore, there are no gaps in the ranks. Hence:

SELECT DISTINCT
  v, 
  DENSE_RANK() OVER (window) row_number
FROM t
WINDOW window AS (ORDER BY v)
ORDER BY v, row_number

Result:

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| b |          2 |
| c |          3 |
| d |          4 |
| e |          5 |
+---+------------+

Example 2: This query is faster than, CTE and you can use DENSE_RANK as a ROW_NUMBER for distinct Records.

SELECT DISTINCT
	name
	,DENSE_RANK() OVER(ORDER BY name) AS DENSE_RankNumber
FROM tbl_numbers

Result:

name       DENSE_RankNumber
---------- --------------------
ABC        1
LMN        2
OPQ        3
XYZ        4

DENSE_RANK: returns a row-by-row unique number in ascending order, starting at 1. The same rank is given to all duplicate rows when there are duplicates, however the DENSE_RANK function does not skip any ranks. As a result, the row immediately after the duplicate rows will have the next rank in the order.

Example 1: We will see a query where there are no duplicates in order to better understand how RANK() and DENSE_RANK() differ when duplicate values are present in the sorting ORDER BY clause. Using DISTINCT on the "first name" column, we can quickly remove duplicate results from any query. For ease of processing, I'll enclose this specific SELECT in a CTE (WITH clause):

mysql-sql [learning]> WITH distinct_names AS (SELECT DISTINCT first_name FROM some_names)
     SELECT dn.first_name,
     RANK() OVER(ORDER BY dn.first_name ASC) AS rnk,
     DENSE_RANK() OVER(ORDER BY dn.first_name ASC) AS d_rnk
     FROM distinct_names AS dn;

Result:

+------------+-----+-------+
| first_name | rnk | d_rnk |
+------------+-----+-------+
| Charlie    |   1 |     1 |
| Humpty     |   2 |     2 |
| Jim        |   3 |     3 |
| Jupyter    |   4 |     4 |
| Mary       |   5 |     5 |
| Max        |   6 |     6 |
| Roger      |   7 |     7 |
+------------+-----+-------+

Both RANK() and DENSE_RANK() assign a sequential integer value when there are no ties in the sorting column(s). This is a crucial distinction because, as we will see in the query instances, both of these Window procedures handle allocating the incrementing integer value differently when there are ties (duplicates) in the sorting column.

Example 2: Consider the below table for understanding rank functions.

Notice 3 employees have the same salary 8000. When you execute the following query you can clearly see the difference between RANK, DENSE_RANK and ROW_NUMBER functions.

SELECT Name, Salary, Gender,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,
RANK() OVER (ORDER BY Salary DESC) AS [Rank],
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees

When you want to sort a row set by one column and return the value of a different column from the first or last row in the ordered set, the FIRST and LAST (also known as "KEEP") aggregate functions come in very handy.

We must specify a tie-breaker for the situation where many rows have the identical first (last) value in order to ensure that the result of the FIRST (LAST) function is deterministic. Applying an aggregate function on the column we want to retrieve is the tie-breaker.

A very unique circumstance calls for the employment of the FIRST function, or more precisely the KEEP FIRST function. Let's say we ranked a set of records and discovered a number of records in the top rank. We now wish to do an aggregate operation on the first-rank records. KEEP FIRST makes that possible.

Syntax:

Function( ) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (<partitioning_clause>)

Please take note that the only analytic functions that differ from the standard syntax are FIRST and LAST. The OVER clause does not contain the ORDER BY clause. Additionally, they oppose any < window> clause. Always use DENSE_RANK for the FIRST and LAST rankings. The below query demonstrates how to use the FIRST function. Similar calculations are made on the last-ranked records using the LAST function.

Example 1: For instance, the following query gives the FIRST_NAME of the employee with the lowest SALARY for each department. The query returns the "minimum" first name if more than one person with the lowest income works for the same department; in this case, the MIN function breaks ties.

select department_id,
  any_value(first_name) keep(dense_rank FIRST order by salary)
from employees
group by department_id;

Example 2: Display the salary of the employees with lowest and highest in the department.

You can use Over clause to make the output more redefine as department wise.

SELECT employee_id,
department_id,
salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary) OVER (PARTITION BY department_id) AS lowest,
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY salary) OVER (PARTITION BY department_id) AS highest
FROM employees
ORDER BY department_id, salary;

Example 3: example for FIRST AND LAST:

SELECT empno,
   deptno,
   sal,
   MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
   MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM   emp
ORDER BY deptno, sal;

Result:

     EMPNO     DEPTNO        SAL     Lowest    Highest
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       5000
      7782         10       2450       1300       5000
      7839         10       5000       1300       5000
      7369         20        800        800       3000
      7876         20       1100        800       3000
      7566         20       2975        800       3000
      7788         20       3000        800       3000
      7902         20       3000        800       3000
      7900         30        950        950       2850
      7654         30       1250        950       2850
      7521         30       1250        950       2850
      7844         30       1500        950       2850
      7499         30       1600        950       2850
      7698         30       2850        950       2850

The window function DENSE_RANK() ranks every row in a partition of a without skipping any rank numbers. The DENSE_RANK() function, as opposed to RANK(), returns rank values in order. If two rows in a partition have the same values, they are ranked equally.

An OLAP ranking function called DENSE_RANK determines a ranking value for each row in an OLAP window. The needed ORDER BY statement in the OVER clause serves as the foundation for the return value, which is an ordinal number.

SQL Server 2005 saw the introduction of DENSE_RANK(), which returns a rank that begins at 1 based on the ordering of the row with no gaps in ranking values. Therefore, DENSE_RANK() produces a rank of the specific row that is equal to one plus all different rank values that came before the individual row. A window function called DENSE_RANK() assigns ranks to rows in the result based on the current partition. Similar to RANK(), if the value is the same for two rows, they will receive the same rank. For example, if there are two rows at rank one and there are a total of four of these rows, DENSE_RANK() will return 1,1,2,3, while RANK() would return 1,1,3,4.

Syntax:

The syntax of the DENSE_RANK() function is as follows:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The ORDER BY clause specifies the order in which the rows of each partition indicated by the PARTITION BY clause are applied by the DENSE_RANK() function. When the partition boundary is crossed, the rank is reset.

Optional: The PARITION BY clause. The function will treat the entire result set as one partition if you omit it.

The DENSE_RANK Function would assign the rank number to each partition if you provided the Partition By Clause.

This function's return type is a bigint. In contrast to deterministic functions, which always return the same result, the DENSE RANK() function is non-deterministic. Even though the database state doesn't change when this function is called, it can return a different result at a different time.

Example 1: DENSE_RANK function

The following query ranks sales people by the amount of their sales. Ranks are consecutive even if multiple sales amounts have the same rank.

SELECT emp_num, sales,
DENSE_RANK() OVER (ORDER BY sales) AS dense_rank,
FROM sales;

Result:

emp_num      sales   dense_rank
    101      2,000      1
    102      2,400      2
    103      2,400      2
    104      2,500      3
    105      2,500      3
    106      2,650      4

Example 2: The following example uses the DENSE_RANK() function to rank products by list prices:

SELECT product_id,
	product_name,
	list_price,
	DENSE_RANK () OVER ( 
		ORDER BY list_price DESC
	) price_rank 
FROM
	production.products;

Example 3:

Step 1: Creating table inserting rows and using DENSE_RANK():

CREATE TABLE dense_rank_tutorial(
alphabet VARCHAR(10)
);

Step 2: Insert table

INSERT INTO dense_rank_tutorial(alphabet) VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');

Step 3: DENSE RANK

SELECT alphabet,
DENSE_RANK() OVER (ORDER BY alphabet) my_dense_rank
FROM dense_rank_tutorial;

We established a table, added values, and used DENSE_RANK() to determine the alphabets' order. The rating has been arranged alphabetically. We use dense rank functions so that the ranks are not skipped and we use the separate rank value that came before B or C and then added 1 to acquire the rankings. B and C are repeated, thus they have the same rank.

Example 4: This SQL DENSE_RANK Function example demonstrates how to densely rank the table's partitioned records. The data will be divided up by occupation in the ranking function query that follows, and a dense rank number will be assigned based on the yearly income.

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,DENSE_RANK() OVER (
    PARTITION BY [Occupation] 
    ORDER BY [YearlyIncome] DESC
    ) AS RANK
  FROM [Customers]

Example 5: The SQL Server statement below partitions the chosen data into groups based on the individuals' occupations. You can see that there are four partitions from the above.

PARTITION BY [Occupation]

In the below statement, we used the DENSE_RANK Function with Partition by clause. So, the function will assign a dense rank number for each individual partition.

DENSE_RANK() OVER (
              PARTITION BY [Occupation] 
              ORDER BY [YearlyIncome] DESC
             ) AS RANK

It has given the same rank to 3 and 4 records because their yearly income is the same. Next, it assigned the 2nd rank to the next record (it won’t skip any numbers).

Calculates a group rank for a hypothetical row that you provide. Rankings are never ignored in this dense rank, even when a group contains rows with the same rank.

The primary distinction is that while a basic aggregate function uses a GROUP BY clause common to all aggregations to decrease the number of rows to match the number of categories to which the data are aggregated, PARTITION BY allows you to define the GROUP BY expression for each aggregate function separately. In contrast, the window function assigns the correct value to each row of the dataset, even if these values are the same.

GROUP BY statement, depending on another variable, to apply the function to subsets or rows.

Example 1: The hypothetical new row (num=4, odd=1dense )'s rank inside each group of rows from test4 is determined by the values in the odd column in the example that follows:

SELECT * FROM test4;

Output:

       NUM        ODD
      ------    --------
         0          0
         1          1
         2          0
         3          1
         3          1
         4          0
         5          1

SELECT odd, DENSE_RANK(4,1) WITHIN GROUP (ORDER BY num, odd)
FROM test4 GROUP BY odd;

Result:

      ODD  DENSE_RANK(4,1)WITHINGROUP(ORDERBYNUM,ODD)
---------- ------------------------------------------
         0                                          4
         1                                          3

In the group odd=0, the new row comes after (0,0), (2,0), and (4,0), and thus it is in position 4.

Example 2: Using aggregate functions and a GROUP BY, the illustration will combine the minimum and maximum values for each currency code:

SELECT ToCurrencyCode,
    MIN(EndOfDayRate) AS min_Rate,
    MAX(EndOfDayRate) AS max_Rate
FROM Sales.CurrencyRate
GROUP BY ToCurrencyCode;

Since the GROUP BY would then include the full primary key of the table, adding the date column would be the same as eliminating the GROUP BY altogether. Because those columns will always display the daily rate, the MIN() and MAX() functions are effectively eliminated:

SELECT CurrencyRateDate, ToCurrencyCode,
    MIN(EndOfDayRate) AS min_EODRate,
    MAX(EndOfDayRate) AS max_EODRate
FROM Sales.CurrencyRate
GROUP BY CurrencyRateDate, ToCurrencyCode;

All columns must either be in an aggregate function or be included in the GROUP BY clause in order for GROUP BY to work on them. Each function is given its own "GROUP Via" by PARTITION BY.

Example 3: By averaging the differences between session start and finish timings, this question can be answered quite simply. The findings may then be aggregated by session type using a GROUP BY statement.

SELECT session_type,
       avg(session_end - session_start) AS duration
FROM twitch_sessions
GROUP BY session_type;

Example 4: The following statement illustrates the use of the DENSE_RANK function:

SELECT s_suppkey, DENSE_RANK()
OVER ( ORDER BY ( SUM(s_acctBal) DESC )
AS rank_dense FROM supplier GROUP BY s_suppkey;

Result:

s_suppkey        sum_acctBal       rank_dense
supplier#011     200,000            1
supplier#002     200,000            1
supplier#013     123,000            2
supplier#004     110,000            3
supplier#035     110,000            3
supplier#006     50,000             4
supplier#021     10,000             5

The function's use of a query result set is indicated by the OVER clause. The rows that are returned following the evaluation of the FROM, WHERE, GROUP BY, and HAVING clauses are known as the result set. The data set of rows to be included in the rank analytical function computation is specified by the OVER clause.

The ascending or descending ordering sequence is specified by the ASC or DESC option. The default order is ascending.

The DENSE_RANK and RANK functions are two more aggregate functions where it may be essential to include nulls in the calculation. Fortunately, DENSE_RANK and RANK include nulls by default, just like FIRST and LAST do.

Specifies whether members with null values should appear first or last in the list.

WITHIN

• Selects a set of related dimension members to be ranked.
• LEVEL ranks all members at the same level.
• PARENT ranks members at the same level with the same parent.
• ANCESTOR ranks all members at the same level and with the same ancestor at a specified level.
• dim_level_id: The name of a level of dimension_id.
• hier_level_id: The name of a level of hierarchy_id.

Example 1: Check for last and fast example, given test data like this (analytic value rankings are included for clarity):

GROUP_KEY  VAL        VAL_DENSE_RANK   VAL_RANK
---------- ---------- -------------- ----------
Group-1    (null)                  1          1
Group-1    (null)                  1          1

Group-2    a                       1          1
Group-2    a                       1          1
Group-2    z                       2          3
Group-2    z                       2          3
Group-2    (null)                  3          5

Group-3    A                       1          1
Group-3    A                       1          1
Group-3    Z                       2          3

The following results show how the aggregate versions of DENSE_RANK and RANK do not ignore nulls.

select group_key ,
  DENSE_RANK( NULL ) WITHIN GROUP ( ORDER BY VAL ) null_dense_rank_within_group ,
  RANK( NULL )       WITHIN GROUP ( ORDER BY VAL ) null_rank_within_group
from t1
group by group_key
order by group_key ;

Output:

GROUP_KEY 	 NULL_DENSE_RANK_WITHIN_GROUP	 NULL_RANK_WITHIN_GROUP
---------- 	---------------------------- 	----------------------
Group-1                               1                      1
Group-2                               3                      5
Group-3                               3                      4

Example 2: This example uses the built-in Calendar hierarchy to rank time periods within a year according to Unit Cost. The ranking for JUN-02 remains at 7, despite the fact that JAN-02 and JUL-02 have the same value and rank (6) as each other.

DENSE_RANK() OVER 
(DIMENSION "TIME" ORDER BY PRICE_CUBE.UNIT_COST DESC NULLS LAST WITHIN ANCESTOR AT DIMENSION LEVEL TIME.CALENDAR_YEAR)

A very helpful function is MAX () KEEP (DENSE_RANK FIRST / LAST), which returns the highest value possible for a field.

To determine the value of the highest record, utilise the MAX function. By including the KEEP clause at the end, it performs an analytical function.

The function is instructed to maintain the first value it discovers when looking for the sales value by the KEEP clause and DENSE_RANK FIRST. However, the ORDER BY start date DESC section instructs the function to sort the fields in descending order according to the start date. This instructs the MAX function to locate the row that matches the start date value with the highest value.

The disadvantage of this approach is that it does not display the whole record.

However, the drawback with this solution is that it does not show the entire record.

Example 1: Getting the data you need is to use the MAX function.

SELECT s.city,
MAX(s.start_date) KEEP (DENSE_RANK FIRST ORDER BY sales DESC) AS start_date
FROM sales_volume s
GROUP BY s.city
ORDER BY s.city;

Now, the highest record's value is determined using the MAX function. By including the KEEP clause at the end, it performs an analytical function.

Example 2: I know that the FIRST and LAST keywords are actually taking care of that for me when I use MIN in queries where I want the first value and max when I want the last value. In these circumstances, is there truly a difference between MIN and MAX:

Select ID, 
       Max(AMT) KEEP (DENSE_RANK LAST ORDER BY Order_Date) Last_amt, 
       Min(AMT) KEEP (DENSE_RANK FIRST ORDER BY Order_Date) First_amt
From ORDERS
where ID = '00010'
group by ID;

Example 3: Make the denserank to max:

SELECT w.dept, MAX(w.salaries) 
KEEP(DENSE_RANK FIRST ORDER BY w.age) max_salary FROM workers w WHERE 1=1 GROUP BY dept;

DENSE_RANK(): attributes the same row number to the same value, leaving no “holes”.

To provide a distinct rank number within the partition in accordance with the specified column value, we use the DENSE RANK() function. It differs somewhat from the Rank function but otherwise is comparable.

If we have duplicate values, SQL also gives different ranks to those rows in the RANK function DENSE_RANK(). Ideally, we ought to obtain the same rank for values that are identical or similar.

Example 1: Let’s execute the following query with the DENSE_RANK() function.

SELECT Studentname, 
       Subject, 
       Marks, 
       DENSE_RANK() OVER(ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY Rank;

Example 2: Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.

select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;

The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

Example 3: Let’s do this by creating a table in sql.

CREATE TABLE test_test AS

SELECT ‘a’ v FROM dual UNION ALL
SELECT ‘a’ FROM dual UNION ALL
SELECT ‘a’ FROM dual UNION ALL
SELECT ‘b’ FROM dual UNION ALL
SELECT ‘c’ FROM dual UNION ALL
SELECT ‘c’ FROM dual UNION ALL
SELECT ‘d’ FROM dual UNION ALL
SELECT ‘e’ FROM dual;

select v, row_number() over (order by v) row_number, 
rank() over (order by v) rank, dense_rank() over (order by v) dense_rank from test_test order by v;

After running this query, that took 2 mapreduce jobs and here is following output.

a 3 1 1
a 2 1 1
a 1 1 1
b 4 4 2
c 6 5 3
c 5 5 3
d 7 7 4
e 8 8 5

Each partition receives the analytic function DENSE_RANK().

The result set is divided into divisions to which the function applies using the PARTITION BY clause. The function interprets the entire result set as one partition if you omit the PARTITION BY clause, which can be bypassed (optional).

Syntax:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

The main difference between Dense Rank and Rank is that Dense_Rank() applies rank values to every row in every partition without any gaps.

Example 1: The DENSE_RANK function functions similarly to the RANK function, except instead of awarding medals for the Olympics, it awards ranks in a sequential order.

SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
FROM   emp;

Result:

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

Example 2: The rankings are grouped using PARTITION BY. The rankings reset when the value for the column mentioned here changes. Imagine if we increased the student's test results by subject. The ranking of each score, categorized by subject, would be provided if the data were partitioned by subject.

SELECT student_name, 
DENSE_RANK() OVER(PARTITION BY subject ORDER BY grades DESC) AS grade_ranking

Example 3: Here for get the records form tracks:

SELECT AlbumId,
	Name,
	Milliseconds,
	DENSE_RANK () OVER ( 
		PARTITION BY AlbumId 
		ORDER BY Milliseconds 
	) LengthRank
FROM
	tracks;

Explanation:

• The tracks were first divided into albums via the PARTITION BY clause.
• The tracks in each album were then ordered by their lengths using the ORDER BY clause.
• The rank for the track in each partition was then calculated using the DENSE_RANK() function, which was applied to each partition separately.

Example 4: This example for withdraw a sales details from sales table:

SELECT sales_employee,
    fiscal_year,
    sale,
    DENSE_RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;

PARTITION BY many columns. Multiple data points can be utilised to divide window averages using the PARTITION BY clause (columns). For instance, you can determine the average goals scored per nation, season, and/or year (taken from the date column).

A subsection of the OVER clause is the PARTITION BY clause. To specify a subset of data in a partition, use the SQL PARTITION BY clause. Each partition receives a separate operation and recalculation of the window function. To divide the result set, you can specify one or more columns or expressions.

PARTITION BY is not required.

The PARTITION attribute contained in the ORDER BY clause has an impact on the DENSE_RANK() function.

All the even, odd, and subsequent values are grouped together when partitioning by the PROPERTY column, and DENSE_RANK() provides the position of each value inside the group, resulting in several ascending sequences.

Example 1: The rankings are grouped using PARTITION BY. The rankings reset when the value for the column mentioned here changes. Imagine if we increased the student's test results by subject. The ranking of each score, categorised by subject, would be provided if the data were partitioned by subject.

SELECT student_name, 
DENSE_RANK() OVER(PARTITION BY subject ORDER BY grades DESC) AS grade_ranking

The rankings are shown in a SQL output table using the PARTITION BY topic and DENSE_RANK functions.

As you can see, each topic's highest grade is given a ranking of 1, and the remaining grades are similarly rated according to where they stand in each subject.

Example 2: This example for sql dense rank partition by multiple columns:

select x, 
dense_rank() over(partition by property order by x) as rank, property from int_t;

Output:

+----+------+----------+
| x  | rank | property |
+----+------+----------+
| 2  | 1    | even     |
| 4  | 2    | even     |
| 6  | 3    | even     |
| 8  | 4    | even     |
| 10 | 5    | even     |
| 7  | 1    | lucky    |
| 7  | 1    | lucky    |
| 7  | 1    | lucky    |
| 1  | 1    | odd      |
| 3  | 2    | odd      |
| 5  | 3    | odd      |
| 7  | 4    | odd      |
| 9  | 5    | odd      |
| 6  | 1    | perfect  |
| 2  | 1    | prime    |
| 3  | 2    | prime    |
| 5  | 3    | prime    |
| 7  | 4    | prime    |
| 10 | 1    | round    |
| 1  | 1    | square   |
| 4  | 2    | square   |
| 9  | 3    | square   |
+----+------+----------+

When duplicate numbers are partitioned by the X column, all the duplicate numbers are grouped together and each value is given its position within the group. Because each value only appears once or twice, DENSE_RANK() places each X value either first or second inside its group.

The group or partition that the function acts over, based on how the supplied condition is judged. A new dynamic partition is formed inside the designated window partition if the condition evaluates to TRUE.

RESET WHEN is not required. The entire result set functions as a single partition if there are no RESET WHEN or PARTITION BY clauses.

The ORDER BY clause must be specified as well if RESET WHEN is.

condition:

Conditional partitioning is decided by a conditional phrase. With the added restriction that nested ordered analytical functions cannot contain a RESET WHEN clause, the condition in the RESET WHEN clause is similar in scope to the condition in a QUALIFY clause. Additionally, SELECT cannot be specified as a nested subquery inside of the condition.

To generate sub-partitions inside the specific window partitions, the condition is applied to the rows in all selected window partitions.

Example 1: We can run a Top-N query for each department, just like we can with the RANK analytical function. The version that follows allocates the dense rank in the inline view and then makes use of that rank to limit the rows to the top two (highest paid) workers in each department.

SELECT *
FROM   (SELECT empno,
    deptno,
    sal,
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS myrank
    FROM   emp)
WHERE  myrank <= 2;

Result:

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7839         10       5000          1
      7782         10       2450          2
      7788         20       3000          1
      7902         20       3000          1
      7566         20       2975          2
      7698         30       2850          1
      7499         30       1600          2

Example 2: Conditions for Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL.

select employee_name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';

A rank would be determined for each distinct pay in the Marketing department using the SQL statement above, which would yield all workers that are employed in the Marketing department. The DENSE_RANK function would return the same rank for two employees if their salaries were equal.

Example 3: Here is the example for sql dense rank where clause :

SELECT ir.IncidentDate,
	ir.NumberOfIncidents,
    -- Fill in each window function and ordering
    -- Note that all of these are in descending order!
	___() OVER (___ ___ ir.NumberOfIncidents ___) AS rownum,
	___() OVER (___ ___ ir.NumberOfIncidents ___) AS rk,
	___() OVER (___ ___ ir.NumberOfIncidents ___) AS dr
FROM dbo.IncidentRollup ir
WHERE
	ir.IncidentTypeID = 3
	AND ir.NumberOfIncidents >= 8
ORDER BY
	ir.NumberOfIncidents DESC;

We want to know how often each event type 3 appears in our data set in this exercise. So that the date with the most events has a row number, rank, and dense rank of 1, and so on, we want to rank the number of incidents in descending order. We will only include dates with at least 8 incidences in order to make it simpler to follow.

All rows in the set of query results are ranked within each window partition in the order indicated by the window's ORDER BY clause. An uninterrupted series of ranking numbers is the output of the DENSE_RANK function.

It specifies the sequence in which the rows for the full input set or each group created by a PARTITION BY clause are evaluated. You can sort by one or more expressions, and you can optionally decide whether to sort in ascending or descending order and whether to place nulls first or last for each expression. If you want the results to be output in a particular order, also include an ORDER BY clause in the outer block of the query because this ORDER BY clause simply specifies the order in which rows are evaluated.

All items in the group created by the PARTITION BY clause are subject to the analytic function when the ORDER BY clause is removed. Depending on the optional window clause, the analysis can be applied to all of the items in the group when the ORDER BY clause is present or only to a subset of them.

Only the columns listed in ORDER BY clauses define the order in which the rows are examined.

The ORDER BY 1 or other integer value is treated inside the OVER clause as a constant sort value (essentially a no-op) rather than referring to column 1, which is one difference between the analytic and outer uses of the ORDER BY clause.

DENSE_RANK executes as follows:

Sorts split rows according to the ORDER BY clause's criteria.

Using a comparison between the ORDER BY values of the previous row and the current row, the current row is ranked as follows:

• The current row receives the same ranking as the previous row if the ORDER BY values are the same.
• The ranking of the current row is increased or decreased by 1 depending on whether the ORDER BY values are different and whether the sort order is ascending or descending when using DENSE_RANK.
• There are no gaps in the ranking order because DENSE_RANK always moves the rankings up one. The number of distinct ORDER BY items that the query returned has the highest rank value.

Syntax:

Syntax for sql dense rank without order by

DENSE_RANK() OVER( 
... [ window-partition-clause ] 
... window-order-clause  )

The following shows the syntax of the DENSE_RANK() function:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Every row in each division determined by the PARTITION BY clause receives the DENSE_RANK() function in the ORDER BY clause's designated sort order. When it crosses the partition line, the rank is reset.

Optional: The PARITION BY clause. The DENSE_RANK() function will treat the entire result set as a single partition if it is skipped.

Example 1: Here’s a basic example showing the usage of the DENSE_RANK() function:

SELECT AlbumId,
  AlbumName,
  ArtistId,
  DENSE_RANK() OVER (ORDER BY ArtistId ASC) 'Rank'
FROM Albums;

Result:

+-----------+--------------------------+------------+--------+
| AlbumId   | AlbumName                | ArtistId   | Rank   |
|-----------+--------------------------+------------+--------|
| 1         | Powerslave               | 1          | 1      |
| 7         | Somewhere in Time        | 1          | 1      |
| 8         | Piece of Mind            | 1          | 1      |
| 9         | Killers                  | 1          | 1      |
| 10        | No Prayer for the Dying  | 1          | 1      |
| 2         | Powerage                 | 2          | 2      |
| 19        | All Night Wrong          | 3          | 3      |
| 20        | The Sixteen Men of Tain  | 3          | 3      |
| 12        | Big Swing Face           | 4          | 4      |
| 4         | Ziltoid the Omniscient   | 5          | 5      |
| 5         | Casualties of Cool       | 5          | 5      |
| 6         | Epicloud                 | 5          | 5      |
| 3         | Singing Down the Lane    | 6          | 6      |
| 16        | Long Lost Suitcase       | 7          | 7      |
| 17        | Praise and Blame         | 7          | 7      |
| 18        | Along Came Jones         | 7          | 7      |
| 11        | No Sound Without Silence | 9          | 8      |
| 21        | Yo Wassup                | 9          | 8      |
| 22        | Busted                   | 9          | 8      |
| 13        | Blue Night               | 12         | 9      |
| 14        | Eternity                 | 12         | 9      |
| 15        | Scandinavia              | 12         | 9      |
+-----------+--------------------------+------------+--------+

Look at the columns for ArtistId and Rank. Every time the ArtistId goes up, the rank goes up as well. This is because each new artist will receive a new rank because I'm sorting by ArtistId.

Example 1: Use DENSE_RANK() and the query is:

SELECT Prod_Id, SUM(OrdCount) AS OrdCount,
DENSE_RANK() OVER (ORDER BY SUM(OrdCount) DESC) AS Ranks
FROM OrderDetail ORDER BY Ranks

Result:

Prod_Id		OrdCount	Ranks
34		412		1
84		347		2
14		264		3
75		253		4
65		209		5
10		199		6
18		188		7
28		188		7
32		171		8
12		163		9

So, we find that Rank() skips the ranking number when it gets same OrdCount but Dense_Rank() maintains ranking order.

Example 2: In case of duplicates, add 2 to the next rank() and add 1 to the next dense_rank():

select s.prod_id, sum(s.amount_sold),
rank() over (order by sum(s.amount_sold) desc) as rank,
dense_rank() over (order by sum(s.amount_sold) desc) as dense_rank
from all_sales s where s.year=1998 and s.amount_sold is not null
group by s.prod_id
order by s.prod_id; 

Result:

   PROD_ID SUM(S.AMOUNT_SOLD)       RANK DENSE_RANK
---------- ------------------ ---------- ----------
        13          936197.53          7          7
        14         2733887.43          2          2
        15         1368317.88          5          5
        16              11.99         60         60
        17         2239127.88          3          3
        18         5477218.04          1          1
        19          182670.35         20         20
        20          990525.95          6          6
        21         1535187.44          4          4
        22           31853.11         54         54
        23           85211.28         36         36
        24          163929.27         22         22
        25          522713.71         13         13
        26          567533.83         12         12
        27          107968.24         30         30
        28          644480.02          9          9
        29          578374.62         11         11
        30            59391.8         48         48
        31           64464.83         45         45
        32           124081.8         26         26

count(f1) returns the Window-specific methods do not support the use of DISTINCT or ORDER BY. COUNT is likely the most popular distinct aggregate (DISTINCT). It resembles the windowed function DENSE_RANK in certain ways (). Unlike ROW_NUMBER(), DENSE_RANK() only increases the row counter when the ordering column(s) do in fact change from one row to the next. When there are ties between values, RANK returns duplicate values in the ranking sequence, whereas DENSE_RANK returns ranking values without gaps.

COUNT(DISTINCT) with two DENSE_RANKs: DENSE_RANK() over (trxn_category order by id ASC, partition by id).

Syntax:

DENSE_RANK is computed through a syntax transformation, as well.

DENSE_RANK() OVER ws

is equivalent to:

COUNT ( DISTINCT ROW ( expr_1, . . ., expr_n ) )
 OVER ( ws RANGE UNBOUNDED PRECEDING )

In the above example, expr_1 through expr_n represent the list of value expressions in the sort specification list of window w1.

Example 1: We can use DENSE_RANK() as a form of windowed distinct count:

SELECT Part, CountCol, ID,
 --- Windowed MAX() on the DENSE_RANK() column:
     MAX(_dense_rank) OVER (
     PARTITION BY Part) AS CountDistinct
FROM (
    SELECT *,
 --- DENSE_RANK() on the CountCol column:
    DENSE_RANK() OVER (
    PARTITION BY Part
    ORDER BY CountCol) AS _dense_rank
    FROM #CountDistinct
    ) AS sub;

The segment and sequence project is where the DENSE_RANK() calculation is displayed (top-center). Table Spool and Stream Aggregate are two ways that the windowed MAX() presents itself. The query is quite effective because it is non-blocking and doesn't need a memory permit.

Example 2: DENSE_RANK() over (partition by id,trxn_category order by id DESC) or a nested MAX(DENSE_RANK):

SELECT ANY_VALUE(employee_name) AS `employee_name`,
    DATE(created_at) AS `shift_date`,
    COUNT(*) OVER (PARTITION BY ANY_VALUE(created_at), ANY_VALUE(shift_id)) AS `shifts_on_day_1`,
​
    (
        dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) asc) +
        dense_rank() over (partition by ANY_VALUE(created_at) order by ANY_VALUE(shift_id) desc) - 1
    ) as `shifts_on_day_2`
​
FROM scores
    GROUP BY employee_name, DATE(created_at);

Any row with the date 2020-04-01 should have shifts_on_day set to 1, and rows with the date 2020-04-02 should have shifts_on_day set to 2.

Although there are millions of entries in the table and only a few hundred are returned by the query, I have thought about utilising a correlated subquery, but it would be a performance nightmare.

Update: In my opinion, the fact that the query already has a group by makes window functions unnecessary. To obtain the average_score of each employee on a given day, all the data must be included in a single query. I can just COUNT(*) to get the final score for each employee.

Create new columns in a table and compare the ranks generated by RANK and DENSE RANK. With other columns, these functions are employed in SELECT queries.

With gaps in the ranking when there are ties, the RANK() function in SQL Server returns the position of a value within the partition of a result set.

With no gaps in the ranking when there are ties, the DENSE_RANK() function in SQL Server provides the position of a value within the partition of a result set.

We utilise the OVER() function after RANK or DENSE_RANK, which requires an ORDER BY clause with the name of the column to sort before determining a ranking.

RANK skips positions following equal ranks, in contrast to DENSE_RANK. The amount of rows with identical rankings determines how many positions were skipped.

For instance, Mary and Lisa both sold the same quantity of goods and are rated #2. The next position with RANK is #4; with DENSE_RANK, it is #3.

Main Differences Between RANK and DENSE_RANK

The SQL language includes a function called rank that aids programmers in classifying various sets of data, while another function called dense rank performs a similar task without omitting any numbers.

While the goal of dense ranking is to just evaluate the rankings of a single column, the goal of ranking is to analyse the given rank of every room.

Rank is written as RANK () while dense rank is written as DENSE_RANK ().

Similar numbers are given the same rank in the ranking, but typically the number after that rank is skipped. However, in dense ranking, no number is left out, and the order of the rankings is strictly numerical.

While the dense ranking is only appropriate for generating data from a certain collection of columns or rows, the ranking makes it simple to obtain results from large amounts of data.

Example 1: Both RANK and RANK_DENSE work on partitions of data:

SELECT RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS r,
  DENSE_RANK() OVER(PARTITION BY month ORDER BY sold products DESC) AS dr,
  first_name,
  last_name,
  month,
  sold products
FROM sales_assistant;

Example 2: Using the RANK() function:

SELECT RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust

Using the DENSE_RANK() function

SELECT DENSE_RANK() OVER (ORDER BY TotCnt DESC) AS TopCustomers, 
CustomerID, TotCnt
FROM (SELECT CustomerID, COUNT(*) AS TotCnt
FROM Orders Group BY CustomerID) AS Cust

Example 3: Let’s say we have two students who have the same grade; both scored 90s on their math test.

Depending on where they rank in relation to the other values, RANK and DENSE_RANK will assign the grades the same rank. The next available ranking value will then be skipped by RANK, however DENSE_RANK will continue to use the following chronological ranking value.

With RANK, the next lowest value would be given a rank of 4, passing over 3, if the two 90s are assigned a ranking of 2. With DENSE_RANK, no values would be skipped and the next-lowest value would be given a rank of 3.

Let’s compare the outcomes of both of these functions.

SELECT student_name, 
RANK() OVER(ORDER BY grades DESC) AS rank_w_rank, 
DENSE_RANK() OVER(ORDER BY grades DESC) AS rank_w_dense_rank

A SQL output table comparing the values of RANK and DENSE_RANK functions:

As you can see once more, rank 2 is not present in the RANK column, whereas rank 2 and rank 4 are present in the DENSE_RANK column, despite there being 5 rows in the table.

Now that you know how to use RANK and DENSE_RANK, you hopefully know when to utilise each. Normally, I use SQL's DENSE_RANK function as my default rank function. I believe there are more issues if you proceed without omitting a number in the order of chronological ranking.