SQL - SQL Server - Find the First Row of Each Group¶

Source: sql-snippets/first-row-of-group.md at main · count/sql-snippets (github.com)

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 SQL - Window Functions:

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 or Common Table Expression 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
table - this is Sales

with and_ranking as (
  select
    *,
    rank() over (partition by City order by Order_Date desc) as ranking
  from Sales
)

select * from and_ranking where ranking = 1

Appendix: Links¶

Backlinks:

list from [[SQL - SQL Server - First Row of Group]] AND -"Changelog"