CODEWITHSUNDEEP
sqlsql-serverselect
Maor Cohen
Maor Cohen

Reputation: 956

Select records with maximum value

I have a table that is called: customers.

I'm trying to get the name and the salary of the people who have the maximum salary.

So I have tried this:

SELECT name, salary AS MaxSalary
FROM CUSTOMERS 
GROUP BY salary
HAVING salary = max(salary)

Unfortunately, I got this error:

Column 'CUSTOMERS.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know I should add the name column to the group by clause, but I get all the records of the table.

I know that I can do it by:

SELECT name, salary
FROM CUSTOMERS
WHERE salary = (SELECT MAX(salary) FROM CUSTOMERS)

But I want to achieve it by group by and having clauses.

Upvotes: 2

Views: 74

Answers (3)

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

Mureinik's answer is good with rank, but if you didn't want a windowed function for whatever reason, you can just use a CTE or a subquery.

with mxs as (
  select
    max(salary) max_salary
  from
    customers
)
select
  name
  ,salary
from
  customers cst
  join mxs on mxs.max_salary = cst.salary

Upvotes: 1

masuduzzaman
masuduzzaman

Reputation: 113

There was no need to use group by and having clause there, you know. But if you want to use them then query should be

SELECT name, salary 
FROM CUSTOMERS 
GROUP BY salary 
having salary = (select max(salary) from CUSTOMERS)

Upvotes: -1

Mureinik
Mureinik

Reputation: 312219

This requirement isn't really suited for a group by and having solution. The easiest way to do so, assuming you're using a modern-insh version of MS SQL Server, is to use the rank window function:

SELECT name, salary
FROM   (SELECT name, salary, RANK() OVER (ORDER BY salary DESC) rk
        FROM   customers) c
WHERE  rk = 1

Upvotes: 3

Related Questions