Find out all the employees who earn highest salary in each job type sort in descending salary order

Hi All,

Could you please help me to get the highest salary for each department. If multiple employees having the highest salary in the same department then I need the get oldest employee details(who joined first in the company).

SOLUTION

Please use the SQL Query below in order to get the highest salary of department:

SELECT DepartmentID, MAX(Salary) FROM Employee GROUP BY DepartmentID

Now for Printing the name, you must join the Employee table with Department table using key DepartmentID,

SELECT DepartmentName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DepartmentId = d.DepartmentID GROUP BY DepartmentName

30 Jul 2015 3:06 pm Sugandh

select * from (select ename,deptno,sal,hiredate,dense_rank() over(partition by deptno order by sal desc,hiredate asc) rn from emp)
where rn=1;

15 Apr 2017 11:34 pm Guest

SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

Read

Discuss

View Discussion

Improve Article

Save Article

Like Article

In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the details of the employees sorted in decreasing order of their salaries. We shall use the ORDER BY clause to achieve this. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table COMPANY inside the database GeeksForGeeks. This table has 4 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_NAME, and SALARY containing the id, name, department, and the salary of various employees.

Query:

CREATE TABLE COMPANY( EMPLOYEE_ID INT PRIMARY KEY, EMPLOYEE_NAME VARCHAR(10), DEPARTMENT_NAME VARCHAR(10), SALARY INT);

Output:

Step 4: Describe the structure of the table COMPANY.

Query:

EXEC SP_COLUMNS COMPANY;

Output:

Step 5: Insert 5 rows into the COMPANY table.

Query:

INSERT INTO COMPANY VALUES(1,'RAM','HR',10000); INSERT INTO COMPANY VALUES(2,'AMRIT','MRKT',20000); INSERT INTO COMPANY VALUES(3,'RAVI','HR',30000); INSERT INTO COMPANY VALUES(4,'NITIN','MRKT',40000); INSERT INTO COMPANY VALUES(5,'VARUN','IT',50000);

Output:

Step 7: Display the details of the employees in the decreasing order of their salaries. We will use the ORDER BY clause along with the DESC clause to sort the rows according to decreasing salaries of the employees. The column name SALARY must be mentioned after the ORDER BY clause to specify the basis of sorting.

Syntax:

SELECT * FROM TABLE_NAME ORDER BY COLUMN DESC;

Query:

SELECT * FROM COMPANY ORDER BY SALARY DESC;

Note: This query returns all the rows in the sorted(reversed) order.

Output:

Last update on August 19 2022 21:51:00 (UTC/GMT +8 hours)

[An editor is available at the bottom of the page to write and execute the scripts.]

51. From the following table, write a SQL query to find those employees who receive maximum salary for a designation. Sort the result-set in descending order by salary. Return complete information about the employees.

Sample table: employees

Sample Solution:

SELECT * FROM employees WHERE salary IN (SELECT max(salary) FROM employees GROUP BY job_name) ORDER BY salary DESC;

Sample Output:

emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (6 rows)

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Sort employees with minimum salary for a designation.
Next SQL Exercise: Recent hires in every department in order of hire date.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

Share this Tutorial / Exercise on : Facebook and Twitter

Get record counts for all tables in MySQL database:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{your_db}';

Database: MySQL

Ref: https://bit.ly/3SkWU8b