CODEWITHSUNDEEP
sqlsql-server
Alexey Kulikov
Alexey Kulikov

Reputation: 1147

select only name by maximal value

I have a request

select
    w.Departament_Id, avg(w.Salary) as avgSal,
    dep.Name as Name
from
    Employee w
join
    Departament dep
on
    dep.Id = w.Departament_Id 
group by
    w.Departament_Id,
    dep.Name

this request retuns a table contains all avg salary for each departament. Next the target is to select Name of departament with a maximal avgSal value. How to solve it?

Upvotes: 0

Views: 34

Answers (1)

D Stanley
D Stanley

Reputation: 152566

sort by the aggregate and take the top 1:

select TOP 1
    w.Departament_Id, 
    avg(w.Salary) as avgSal,
    dep.Name as Name
from Employee w
join Departament dep
    on dep.Id = w.Departament_Id 
group by
    w.Departament_Id,
    dep.Name
ORDER BY avg(w.Salary) DESC

The syntax may be slightly different depending on your server software (some will allow ORDER BY avgSal, some will not; some will use LIMIT instead of TOP), but that's the general idea.

Upvotes: 2

Related Questions