How to Get First Row Per Group in SQL Server

In SQL Server, you often need to retrieve the first row per group in your dataset. This can be accomplished using window functions like ROW_NUMBER() or RANK(). These functions are invaluable when you need to identify the first row in each group based on a specific sorting order.

Scenario

Imagine you have a sales table where each salesperson's sales are recorded. You want to find the first sale per salesperson based on the sale date.

Solution Using ROW_NUMBER()

The ROW_NUMBER() function assigns a unique number to each row within a partition of a result set, starting at 1 for the first row. You can use it to get the first row per group by partitioning the data by a column (e.g., salesperson) and ordering by another column (e.g., sale date).

Example:

SELECT SalesPersonID, SaleDate, SaleAmount
FROM (
    SELECT SalesPersonID, SaleDate, SaleAmount,
           ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) AS RowNum
    FROM Sales
) AS Subquery
WHERE RowNum = 1;
                                

This query partitions the data by SalesPersonID, orders it by SaleDate, and assigns a row number to each row. The outer query then filters to only include rows where the row number is 1, which corresponds to the first sale per salesperson.

Alternative Method: Using RANK()

If you need to handle ties (i.e., when two rows have the same value in the column you're sorting by), you can use the RANK() function instead of ROW_NUMBER(). The RANK() function will assign the same rank to rows with the same value in the sorted column, and you can filter based on the rank.

Example:

SELECT SalesPersonID, SaleDate, SaleAmount
FROM (
    SELECT SalesPersonID, SaleDate, SaleAmount,
           RANK() OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) AS SaleRank
    FROM Sales
) AS Subquery
WHERE SaleRank = 1;
                                

This works similarly to the ROW_NUMBER() query, but in case of ties, multiple rows could be returned for the same salesperson.

Conclusion

Retrieving the first row per group in SQL Server is straightforward using the ROW_NUMBER() or RANK() window functions. These functions provide powerful tools for handling complex queries and ensuring you retrieve exactly the data you need, efficiently and effectively.