QUALIFY by ROW_NUMBER for cleaner queries

QueryStash

QUALIFY by ROW_NUMBER for cleaner queries

1SELECT *
2FROM some_table
3QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY report_date DESC) = 1;
4
5-- INSTEAD OF --
6
7SELECT * EXCEPT(rn)
8FROM (
9  SELECT
10    *
11    , ROW_NUMBER() OVER (PARTITION BY id ORDER BY report_date DESC) AS rn
12  FROM some_table
13)
14WHERE rn = 1

SELECT *
FROM some_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY report_date DESC) = 1;

-- INSTEAD OF --

SELECT * EXCEPT(rn)
FROM (
  SELECT
    *
    , ROW_NUMBER() OVER (PARTITION BY id ORDER BY report_date DESC) AS rn
  FROM some_table
)
WHERE rn = 1

Public

bigquery

QueryStash

Created: Apr 4th 2022

QueryStash

Made with ❤️ by @Yekalb

Twitter Facebook Email

Resources

Blog Pricing Your First Query Search Your Stash

Examples

Merge Statements Persistent Functions Spike Detection QUALIFY Statement BigQuery ML Models

Stay up to date