Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

Select First Row in each SQL Group By group

by suresh

How to Select First Row in each SQL Group By group with example. For this SQL Select first row in each group example, 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 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, ROW_NUMBER is going to select the First row from each group.
-- 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 SQL Server 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

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 SQL 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
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

Placed Under: SQL Interview Questions

  • SQL DML, DDL, DCL & TCL Cmds
  • SQL NOT EXISTS Operator
  • SQL UPDATE from SELECT
  • SQL AFTER UPDATE Triggers
  • SQL Get Column Names in Table
  • SQL IF ELSE
  • SQL ACID Properties
  • SQL FOR XML PATH
  • Java Two Dimensional Array
  • Java Perfect Number Program
  • Java Count Digits in a Number
  • C Compare Two Strings Program
  • C Print Prime Numbers 1 to 100
  • C program to Reverse a String
  • C Palindrome Number Program
  • C Program for Palindrome String
  • C Remove Duplicate String Chars
  • C Square of a Number Program
  • C Sum and Average of N Number
  • Python Fibonacci Series program
  • Python Area Of Circle Program
  • Python Prime Numbers 1 to 100
  • Python Program for Leap Year
  • Tableau Rank Calculation
  • Tableau Google Maps usage
  • Power BI Format Dates
  • Power BI Top 10 Filters
  • Power BI – Create Hierarchy
  • Power BI DAX Math Functions
  • Learn SSIS in 28 Days
  • SSIS Transformations
  • SSIS Incremental Load
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • R Programming Tutorial
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy