KATER__ROW_NUMBER

Family: Windows

Syntax

1
KATER__ROW_NUMBER(field, order_direction) OVER ([PARTITION BY ...])

Arguments

Returns

A whole number. Each row gets its own number. 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.
• Rows that tie have no fixed order, so their numbers can change from one run to the next.
• Empty values come last.

Example

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

1
SELECT
2
  orders.currency AS "Currency",
3
  orders.channel AS "Channel",
4
  EVAL(orders.sum__amount) AS "Revenue",
5
  CUSTOM_CALCULATION(
6
    'row_number__sum__amount',
7
    KATER__ROW_NUMBER(orders.sum__amount, 'desc') OVER (PARTITION BY orders.currency)
8
  ) AS "Place in Currency"
9
FROM kater_dataset.orders AS orders
10
WHERE
11
  KATER__IS_ONE_OF(orders.channel, 'email', 'paid', 'web')
12
GROUP BY
13
  orders.currency,
14
  orders.channel
15
ORDER BY
16
  1,
17
  4

Compiles to

1
ROW_NUMBER() OVER (PARTITION BY orders.currency ORDER BY orders.sum__amount DESC)

1
ROW_NUMBER() OVER (PARTITION BY orders.currency ORDER BY orders.sum__amount DESC NULLS LAST)

See also

• Running totals, ranks and windows
• Other functions in Windows: KATER__WINDOW_AGGREGATION, KATER__LAG, KATER__LEAD, KATER__FIRST_VALUE, KATER__LAST_VALUE, KATER__DELTA, KATER__PERCENT_DELTA, KATER__RANK, KATER__SHARE_OF_TOTAL