Yandex Cloud
  • Services
  • Solutions
  • Why Yandex Cloud
  • Blog
  • Pricing
  • Documentation
  • Contact us
Get started
Language / Region
Yandex project
© 2023 Intertech Services AG
Yandex DataLens
  • Getting started
  • Tutorials
  • Concepts
  • Step-by-step guides
  • Access management
  • Pricing policy
  • Visualization reference
  • Function reference
    • All Functions
    • Aggregate functions
    • Array functions
    • Date/Time functions
    • Logical functions
    • Mathematical functions
    • Operators
    • String functions
    • Text markup functions
    • Time series functions
    • Type conversion functions
    • Window functions
      • Overview
      • AVG
      • AVG_IF
      • COUNT
      • COUNT_IF
      • FIRST
      • LAG
      • LAST
      • MAVG
      • MAX
      • MCOUNT
      • MIN
      • MMAX
      • MMIN
      • MSUM
      • RANK
      • RANK_DENSE
      • RANK_PERCENTILE
      • RANK_UNIQUE
      • RAVG
      • RCOUNT
      • RMAX
      • RMIN
      • RSUM
      • SUM
      • SUM_IF
    • Function Availability
  • Function tutorials
  • Courses
  • Troubleshooting
    • Questions and answers
    • DataLens errors
  1. Function reference
  2. Window functions
  3. RANK

RANK (window)

Written by
Yandex Cloud
,
improved by
amatol

Syntax

Standard
Extended
RANK( value [ , direction ] )
RANK( value [ , direction ]
      [ TOTAL | WITHIN ... | AMONG ... ]
      [ BEFORE FILTER BY ... ]
    )

More info:

  • TOTAL, WITHIN, AMONG
  • BEFORE FILTER BY

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 3, so, in effect, it is rank with 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_DENSE, RANK_UNIQUE, RANK_PERCENTILE.

Argument types:

  • value — Boolean | Date | Datetime | Fractional number | Integer | String | UUID
  • direction — 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].

Result

[City] SUM([Orders]) RANK(SUM([Orders]), "desc") RANK(SUM([Orders]), "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].

Result

[City] [Category] SUM([Orders]) RANK(SUM([Orders]) TOTAL) RANK(SUM([Orders]) WITHIN [City]) RANK(SUM([Orders]) AMONG [City])
'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 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, Oracle Database 12c (12.1), PostgreSQL 9.3.

Was the article helpful?

Language / Region
Yandex project
© 2023 Intertech Services AG