Skip to main content

Categories: Window

ROW_NUMBER

Returns the row number for the current row based on the ORDER BY clause within each partition.

Syntax​

ROW_NUMBER() OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression]) → bigint​

  • partition_expression (optional): An expression that groups rows into partitions.
  • order_expression (optional): An expression that specifies the order of the rows within each partition.

Examples

ROW_NUMBER example 
SELECT "Category", 
  "Descript", 
  "DayOfWeek",
  ROW_NUMBER() 
    OVER (
      PARTITION BY "Category" 
      ORDER BY "DayOfWeek")
FROM Samples."samples.dremio.com"."SF_incidents2016.json"
-- Category, Descript, DayOfWeek, EXPR$3
-- ARSON, ARSON, Friday, 1 
-- ARSON, ARSON, Friday, 2

Usage Notes​

The returned rows are ordered exactly the same with each execution unless identical values are detected in the same partition. Rows containing identical values in the partition and ORDER BY columns receive different row numbers. The order of these row numbers can vary by execution, causing inconsistent query results.