RANK_DENSE (window)
Syntax
RANK_DENSE( value [ , direction ] )
RANK_DENSE( value [ , direction ]
[ TOTAL | WITHIN ... | AMONG ... ]
[ BEFORE FILTER BY ... ]
)
More info:
Description
Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value used for sorting have the same rank. If the first two rows both have rank of 1, then the next row (if it features a different value) will have rank 2, (rank without gaps).
If direction is "desc" or omitted, then ranking is done from greatest to least, if "asc", then from least to greatest.
See also RANK, RANK_UNIQUE, RANK_PERCENTILE.
Argument types:
value—Boolean | Date | Datetime | Fractional number | Integer | String | UUIDdirection—String
Return type: Integer
Note
Only constant values are accepted for the arguments (direction).
Examples
Example with two arguments
Source data
| Date | City | Category | Orders | Profit |
|---|---|---|---|---|
'2019-03-01' |
'London' |
'Office Supplies' |
8 |
120.80 |
'2019-03-04' |
'London' |
'Office Supplies' |
2 |
100.00 |
'2019-03-05' |
'London' |
'Furniture' |
1 |
750.00 |
'2019-03-02' |
'Moscow' |
'Furniture' |
2 |
1250.50 |
'2019-03-03' |
'Moscow' |
'Office Supplies' |
4 |
85.00 |
'2019-03-01' |
'San Francisco' |
'Office Supplies' |
23 |
723.00 |
'2019-03-01' |
'San Francisco' |
'Furniture' |
1 |
1000.00 |
'2019-03-03' |
'San Francisco' |
'Furniture' |
4 |
4000.00 |
'2019-03-02' |
'Detroit' |
'Furniture' |
5 |
3700.00 |
'2019-03-04' |
'Detroit' |
'Office Supplies' |
25 |
1200.00 |
'2019-03-04' |
'Detroit' |
'Furniture' |
2 |
3500.00 |
Grouped by [City].
Sorted by [City].
Formulas:
- City:
[City]; - Order Sum:
SUM([Orders]); - RANK_DENSE desc:
RANK_DENSE(SUM([Orders]), "desc"); - RANK_DENSE asc:
RANK_DENSE(SUM([Orders]), "asc").
Result
| City | Order Sum | RANK_DENSE desc | RANK_DENSE asc |
|---|---|---|---|
'Detroit' |
32 |
1 |
4 |
'London' |
11 |
3 |
2 |
'Moscow' |
6 |
4 |
1 |
'San Francisco' |
28 |
2 |
3 |
Example with grouping
Source data
| Date | City | Category | Orders | Profit |
|---|---|---|---|---|
'2019-03-01' |
'London' |
'Office Supplies' |
8 |
120.80 |
'2019-03-04' |
'London' |
'Office Supplies' |
2 |
100.00 |
'2019-03-05' |
'London' |
'Furniture' |
1 |
750.00 |
'2019-03-02' |
'Moscow' |
'Furniture' |
2 |
1250.50 |
'2019-03-03' |
'Moscow' |
'Office Supplies' |
4 |
85.00 |
'2019-03-01' |
'San Francisco' |
'Office Supplies' |
23 |
723.00 |
'2019-03-01' |
'San Francisco' |
'Furniture' |
1 |
1000.00 |
'2019-03-03' |
'San Francisco' |
'Furniture' |
4 |
4000.00 |
'2019-03-02' |
'Detroit' |
'Furniture' |
5 |
3700.00 |
'2019-03-04' |
'Detroit' |
'Office Supplies' |
25 |
1200.00 |
'2019-03-04' |
'Detroit' |
'Furniture' |
2 |
3500.00 |
Grouped by [City], [Category].
Sorted by [City], [Category].
Formulas:
- City:
[City]; - Category:
[Category]; - Order Sum:
SUM([Orders]); - RANK_DENSE TOTAL:
RANK_DENSE(SUM([Orders]) TOTAL); - RANK_DENSE WITHIN:
RANK_DENSE(SUM([Orders]) WITHIN [City]); - RANK_DENSE AMONG:
RANK_DENSE(SUM([Orders]) AMONG [City]).
Result
| City | Category | Order Sum | RANK_DENSE TOTAL | RANK_DENSE WITHIN | RANK_DENSE AMONG |
|---|---|---|---|---|---|
'Detroit' |
'Furniture' |
7 |
4 |
2 |
1 |
'Detroit' |
'Office Supplies' |
25 |
1 |
1 |
1 |
'London' |
'Furniture' |
1 |
8 |
2 |
4 |
'London' |
'Office Supplies' |
10 |
3 |
1 |
3 |
'Moscow' |
'Furniture' |
2 |
7 |
2 |
3 |
'Moscow' |
'Office Supplies' |
4 |
6 |
1 |
4 |
'San Francisco' |
'Furniture' |
5 |
5 |
2 |
2 |
'San Francisco' |
'Office Supplies' |
23 |
2 |
1 |
2 |
Data source support
ClickHouse 21.8, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3.