CODEWITHSUNDEEP
google-bigquerydistinctwindow-functionsdistinct-on
François Leblanc
François Leblanc

Reputation: 1760

What is the BigQuery equivalent of PostgreSQL's `DISTINCT ON`?

I am migrating some queries from PostgreSQL dialect over to BigQuery. One nice pattern in PostgreSQL is DISTINCT ON (key), which returns the first row for every key based on the sequence as defined in the ORDER BY section.

Is there a similar pattern I can use in BigQuery that would avoid wrapping the query as a CTE?

PostgreSQL query:

SELECT
    DISTINCT ON (user_id)
    user_id,
    updated_on,
    net_change
FROM
    table_name
ORDER BY
    user_id, updated_on DESC

Upvotes: 6

Views: 5302

Answers (1)

François Leblanc
François Leblanc

Reputation: 1760

The BigQuery equivalent of the PostgreSQL dialect's DISTINCT ON (key) is the QUALIFY clause with a window function:

SELECT
    user_id,
    updated_on,
    net_change
FROM
    table_name
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_on DESC) = 1

Upvotes: 11

Related Questions