First row of each group

Explore this snippet here.

Description

Finding the first value in a column partitioned by some other column is simple in SQL, using the GROUP BY clause. Returning the rest of the columns corresponding to that value is a little trickier. Here’s one method, which relies on the RANK window function:

with and_ranking as (
  select
    *,
    rank() over (partition by <partition> order by <ordering>) ranking
  from <table>
)
select * from and_ranking where ranking = 1

where

  • partition - the column(s) to partition by
  • ordering - the column(s) which determine the ordering defining ‘first’
  • table - the source table

The CTE and_ranking adds a column to the original table called ranking, which is the order of that row within the groups defined by partition. Filtering for ranking = 1 picks out those ‘first’ rows.

Thank you!
Also, QUALIFY can be used to apply the filter on the same query.

SELECT
  *
FROM <table>
QUALIFY RANK() OVER (PARTITION BY <partition> ORDER BY <ordering>) = 1

Oh that’s nice, I didn’t know QUALIFY existed - thanks for the tip. Looks like it was just introduced alongside PIVOT and UNPIVOT: Release notes  |  BigQuery  |  Google Cloud

1 Like