Select Rows with Maximum Value on a Column in SQL Server

In this article, we will see 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, you can use the below statement. As you can see, we used Max function along with Group By

SELECT Occupation
      ,MAX([Sales]) AS MaxSale
  FROM [Employee]
  GROUP BY Occupation

Select Rows with Maximum Value on a Column Example 2

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

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

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

Select Rows with Maximum Value on a Column Example 3

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

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

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

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