DENSE_RANK (U-SQL)

  • Article

Summary

The DENSE_RANK ranking function returns the rank of rows within the partition of a window, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

DENSE_RANK can only be used in the context of a windowing expression.

Syntax

DENSE_RANK_Expression :=                                                                                 
    'DENSE_RANK' '(' ')'.

Return Type

The return type is long?.

Usage in Windowing Expression

This ranking function can be used in a windowing expression with the following restrictions:

  • The ORDER BY clause in the OVER operator is required.
  • The ROWS clause in the OVER operator is not allowed.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.

  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

  • The examples below are based on the dataset defined below. Ensure your execution includes the rowset variable.

    @employees = 
    SELECT * FROM 
        ( VALUES
        (1, "Noah",   "Engineering", 100, 10000),
        (2, "Sophia", "Engineering", 100, 15000),
        (3, "Liam",   "Engineering", 100, 30000),
        (4, "Amy",    "Engineering", 100, 35000),
        (5, "Justin", "Engineering", 100, 15000),
        (6, "Emma",   "HR",          200, 8000),
        (7, "Jacob",  "HR",          200, 8000),
        (8, "Olivia", "HR",          200, 8000),
        (9, "Mason",  "Executive",   300, 50000),
        (10, "Ava",   "Marketing",   400, 15000),
        (11, "Ethan", "Marketing",   400, 9000) 
        ) AS T(EmpID, EmpName, DeptName, DeptID, Salary);

A. Ranking all rows in a result set
The following example returns all records ranked by salary. Because a PARTITION BY clause was not specified, the DENSE_RANK function was applied to all rows in the result set.

@result =
    SELECT *,
           DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRankAll
    FROM @employees;

OUTPUT @result
TO "/Output/ReferenceGuide/Ranking/dense_rank/exampleA.csv"
ORDER BY Salary DESC
USING Outputters.Csv();

B. Ranking rows within a partition
The following example ranks the salary per department. The result set is partitioned by DeptID and logically ordered by Salary.

@result =
    SELECT *,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS DenseRankByDept
    FROM @employees;

OUTPUT @result
TO "/Output/ReferenceGuide/Ranking/dense_rank/exampleB.csv"
ORDER BY DeptID ASC,
         Salary DESC
USING Outputters.Csv();

See Also