Find the 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.

Example

Using product sales as an example data source, we can pick the rows of the table corresponding to the most-recent order per city. Filling in the template above:

  • partition - this is just City
  • ordering - this is Order_Date desc, Row_ID desc - the extra ordering by Row_ID is to break ties where multiple sales occurred on the same day
  • table - this is Sales