Menu
A table consists of employee name, address, phone, department, salary:
How to get the highest paid employee row from each department?
I tried with
select dept, max(salary) from employee group by dept
but it gives only two columns. But I want to select an entire row. How to do it?
Alternatively how to add more columns to the result?
(I am using SQL Server 2008)
395
To find the highest salary in the table, write the following query. SELECT MAX(SALARY) FROM Employee; This will give you the output as 15000, i.e the highest salary in the table above.
To enter the Number 1 argument, click inside cell D27 and you'll see the cell selected, then Select the cells till D43. So that column range will get selected, i.e. D27: D43. Click ok after entering the number1 argument. =MAX(D27: D43), i.e. returns the maximum salary of the employee, i.e. 95,916 as a result.
Here is a way to do this task using dense_rank() function. Query : select * from( select ename, sal, dense_rank() over(order by sal desc)r from Employee) where r=&n; To find to the 2nd highest sal set n = 2 To find 3rd highest sal set n = 3 and so on.
Second Maximum Salary in MySQL using LIMIT SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1; In this solution, we have first sorted all salaries from the Employee table in decreasing order, so that the 2 highest salaries come at top of the result set.
You simply need to join the query you currently have back to the employee table to get the full employee information.
select e.*
from employee e
inner join (select dept, max(salary) ms from employee group by dept) m
on e.dept = m.dept and e.salary = m.ms
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
