Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Windows

KATER__RANK

The value's rank among the rows, with 1 for the largest or the smallest value.

Family: Windows

Syntax 

KATER__RANK(field, order_direction) OVER ([PARTITION BY ...])

Arguments

ArgumentDescription
fieldThe value to rank by, such as orders.sum__amount.
order_direction'desc' gives rank 1 to the largest value, and 'asc' to the smallest. One of 'asc', 'desc'.

Returns

A whole number. Rows that tie share a rank, and the next rank is skipped: 1, 2, 2, 4. The field menu writes @format number(0) beside it.

Usage notes

  • Write OVER (PARTITION BY ...) to work within each group, or OVER () for every row. It orders the rows itself, so its OVER takes no ORDER BY.
  • Empty values rank last.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

SELECT
  orders.region AS "Region",
  EVAL(orders.count_distinct__order_id) AS "Orders",
  CUSTOM_CALCULATION(
    'rank__count_distinct__order_id',
    KATER__RANK(orders.count_distinct__order_id, 'desc') OVER ()
  ) AS "Rank"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.region
ORDER BY
  3
RegionOrdersRank
EMEA7771
LATAM7602
AMER7283
APAC6714
null645

Compiles to 

RANK() OVER (ORDER BY orders.count_distinct__order_id DESC)
RANK() OVER (ORDER BY orders.count_distinct__order_id DESC NULLS LAST)

See also