Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Top N

KATER__IS_IN_TOP

True where the value ranks in the top count of the query's rows, ties included.

Family: Top N

Syntax 

KATER__IS_IN_TOP(field, count) [OVER (PARTITION BY ...)]

Arguments

ArgumentDescription
fieldThe value to rank by, usually a measure’s value, such as EVAL(orders.sum__amount).
countHow many ranks to keep. A whole number.

Returns

TRUE or FALSE.

Usage notes

  • Write it in QUALIFY. It ranks the rows of the query’s result.
  • It compiles to (RANK() OVER (ORDER BY field DESC NULLS LAST) <= count AND NOT field IS NULL). Rows that tie all stay, so more than count rows can come back.
  • Write OVER (PARTITION BY ...) to rank within each value of those fields, such as each currency. It orders its own rows, so its OVER takes no ORDER BY.
  • An empty value is never in the top count.
  • It cannot rank by a window value, such as a rank or a running total.
  • You write count in the call. A parameter reaches only an OPTIONAL_FILTER in WHERE, so the compiler refuses one in QUALIFY with parameterized_predicate_not_optional.

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"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.region
QUALIFY
  KATER__IS_IN_TOP(orders.count_distinct__order_id, 2)
ORDER BY
  2 DESC
RegionOrders
EMEA777
LATAM760

Compiles to 

(
  RANK() OVER (ORDER BY orders.count_distinct__order_id DESC) <= 2
  AND NOT orders.count_distinct__order_id IS NULL
)
(
  RANK() OVER (ORDER BY orders.count_distinct__order_id DESC NULLS LAST) <= 2
  AND NOT orders.count_distinct__order_id IS NULL
)

See also