ROW_NUMBER() functions in BigQuery

The ROW_NUMBER() function assigns a unique sequential integer to each row in a result set based on a specified order. Even if values are tied, each row receives a different row number.

ROW_NUMBER() Function Syntax

ROW_NUMBER() OVER (ORDER BY column_name ASC | DESC)

Syntax Explanation

ROW_NUMBER() → Generates a unique row number for each row.
OVER() → Defines how row numbers are calculated.
ORDER BY → Determines the sequence in which rows are numbered.

Example: Create Dataset and Table in BigQuery

Step 1: Create Dataset

SQL Query

CREATE SCHEMA IF NOT EXISTS window_practice;

Step 2: Create Table

SQL Query

CREATE OR REPLACE TABLE window_practice.sales_data (
  sale_date DATE,
  employee STRING,
  sales_amount INT64
);

Step 3: Insert Sample Data

SQL Query

INSERT INTO window_practice.sales_data
VALUES
('2024-01-01', 'Ashish', 500),
('2024-01-02', 'Ashish', 700),
('2024-01-03', 'Ashish', 650),
('2024-01-04', 'Ashish', 800),

('2024-01-01', 'Rahul', 400),
('2024-01-02', 'Rahul', 420),
('2024-01-03', 'Rahul', 390),
('2024-01-04', 'Rahul', 500);

View Data

SQL Query

SELECT *
FROM `ashishcoder.window_practice.sales_data`;

Example: ROW_NUMBER() Based on Sales Amount

Assign row numbers to employees based on their sales_amount in descending order.

SQL Query

SELECT
  sale_date,
  employee,
  sales_amount,

  ROW_NUMBER()
    OVER (
      ORDER BY sales_amount DESC
    ) AS row_number_sales

FROM window_practice.sales_data;

Previous Next