Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the highest paid employee row

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)

like image 395
dotNETbeginner Avatar asked Aug 31 '11 19:08

dotNETbeginner


People also ask

How can I get maximum salary from employee table?

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.

How do I find the highest paid employee in Excel?

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.

How do you find three highest salary records from an employee table?

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.

How do you find the second highest salary from an employee table?

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.


1 Answers

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
like image 172
Derek Kromm Avatar answered Nov 23 '22 18:11

Derek Kromm