How to Select First Row in each SQL Group By group with example. For this SQL Select first row in each group example, We use the below shown data
SQL ROW_NUMBER Function Example
The SQL ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example, we show you how to Select First Row from each SQL Group. The following Query will
- First, partition the data by Occupation and assign the rank number using the yearly income.
- Next, ROW_NUMBER is going to select the First row from each group.
-- Select First Row in each SQL Group By group
-- Using CTE to save the grouping data
WITH groups AS (
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
)
SELECT * FROM groups
WHERE groups.[ROW NUMBER] = 1
Let me show you the SQL Server output of the Select statement inside the CTE
We are extracting all the records whose Row Number is equal to 1.
The below statement divides the data into partition using their Occupation, and then we sort the partitioned data in the descending order using their [yearly income]. From the above you can observe, We got four partitions
PARTITION BY [Occupation] ORDER BY [YearlyIncome] DESC
Next, we used the ROW_NUMBER() function to assign the rankings to the records. I suggest you refer to the ROW_NUMBER article.
ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
Select First Row in each Group Example 2
In this example, we are using the subquery to Select First Row in each Group By group.
-- Select First Row in each SQL Group By group
SELECT * FROM (
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,ROW_NUMBER() OVER (
PARTITION BY [Occupation]
ORDER BY [YearlyIncome] DESC
) AS [ROW NUMBER]
FROM [Customers]
) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.YearlyIncome DESC
