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.