Sometimes, we want to get the top 1 row of each group with SQL.

In this article, we’ll look at how to get the top 1 row of each group with SQL.

How to get the top 1 row of each group with SQL?

To get the top 1 row of each group with SQL, we can use a subquery to assign a row number for each group item.

And then we can use WHERE to select the top row.

For instance, we write

WITH groups AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM logs
)
SELECT *
FROM groups
WHERE rn = 1

to assign a row number for each row with ROW_NUMBER.

And we divide the rows into groups with PARTITION .

We name the subquery with WITH and we set the name to groups.

Next, we get the row from each group with rn row number set to 1 to get the top row of each group.

Conclusion

To get the top 1 row of each group with SQL, we can use a subquery to assign a row number for each group item.

And then we can use WHERE to select the top row.