Tutorial Gateway

Select First Row in each SQL Group By group

by

In this article we will show you, How to Select First Row in each SQL Group By group with example. For this, We are going to use the below shown data

Select First Row in each SQL Group By group 1

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 will show you, How to Select First Row from each SQL Group. The following SQL Query will

  • First, partition the data by Occupation and assign the rank number using the yearly income.
  • Next, it is going to select the First row from each group.

T-SQL CODE

-- Select First Row in each SQL Group By group
USE [SQL Tutorial]
GO
-- 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

OUTPUT: Let me show you the output of the Select statement inside the CTE

Select First Row in each SQL Group By group 2

OUTPUT 2: Now we are extracting all the records whose Row Number is equal to 1.

Select First Row in each SQL Group By group 3

ANALYSIS

Below statement will divide the data into partition using their Occupation, and then we are 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 to refer SQL ROW_NUMBER article.

ROW_NUMBER() OVER (
              PARTITION BY [Occupation] 
              ORDER BY [YearlyIncome] DESC
             ) AS [ROW NUMBER]

Select First Row in each SQL Group By group Example 2

In this example we will show you, How to Select First Row in each SQL Group By group using sub query.

T-SQL CODE

-- Select First Row in each SQL Group By group
USE [SQL Tutorial]
GO
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

OUTPUT

Select First Row in each SQL Group By group 4

Thank You for Visiting Our Blog