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 examples. 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 the Maximum value, you can use the statement below. As you can see, we used the SQL MAX function along with SQL 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 the 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 SQL INNER JOIN to join the employee table with itself. Learn more on the SQL Server tutorial page.

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 SQL SELECT statement in the SQL 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 SQL LEFT JOIN along with SQL 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