Select Rows with Maximum Value on a Column in SQL Server

In this article, we will show you, How to Select Rows with Maximum Value on a Column in SQL Server with example. For this, We are going to use the below shown data

Select Rows with Maximum Value on a Column in SQL Server Example 1

If you are returning the group column, and the column with Maximum value then you can use the below statement. As you can see, we used Max function along with Group By

USE [SQL Tutorial]
GO
SELECT Occupation
      ,MAX([Sales]) AS MaxSale
  FROM [Employee]
  GROUP BY Occupation

OUTPUT

Select Rows with Maximum Value on a Column in SQL Server Example 2

In this example, we will show you, How to select rows with max value along with remaining columns. This is useful if you want to return the remaining columns (non group by columns).

For this example, we used the Inner Join to join the employee table with itself.

USE [SQL Tutorial]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[Occupation]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Emp.[HireDate]
  FROM [Employee] AS Emp
INNER JOIN 
	(
		SELECT Occupation
		  ,MAX([Sales]) AS Sales
		FROM [Employee]
		GROUP BY Occupation
	) AS Emp2
		ON Emp.Occupation = Emp2.Occupation AND Emp.Sales = Emp2.Sales

OUTPUT

Select Rows with Maximum Value on a Column in SQL Server Example 3

This is another approach. Here, we are using the Select statement in Where Clause.

USE [SQL Tutorial]
GO
SELECT *
  FROM [Employee] AS Emp1
  WHERE Sales = 
	(	SELECT MAX([Sales]) AS Sales
		FROM [Employee] AS Emp2 WHERE Emp1.Occupation = Emp2.Occupation 
	)

OUTPUT

SQL Select Rows with Maximum Value on a Column Example 3

In this example we used the Left Join along with Is Null.

USE [SQL Tutorial]
GO
SELECT Emp.*
  FROM [Employee] AS Emp
LEFT JOIN [Employee] AS Emp2
		ON Emp.Occupation = Emp2.Occupation AND Emp.Sales < Emp2.Sales
		WHERE Emp2.EmpID IS NULL

OUTPUT