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 DepartmentIDNow 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;
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 GeeksForGeeksOutput:
Step 2: Use the GeeksForGeeks database. For this use the below command.
Query:
USE GeeksForGeeksOutput:
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:
[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: //bit.ly/3SkWU8b
- Exercises: Weekly Top 16 Most Popular Topics
- SQL Exercises, Practice, Solution - JOINS
- SQL Exercises, Practice, Solution - SUBQUERIES
- JavaScript basic - Exercises, Practice, Solution
- Java Array: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : Conditional Statement
- HR Database - SORT FILTER: Exercises, Practice, Solution
- C Programming Exercises, Practice, Solution : String
- Python Data Types: Dictionary - Exercises, Practice, Solution
- Python Programming Puzzles - Exercises, Practice, Solution
- C++ Array: Exercises, Practice, Solution
- JavaScript conditional statements and loops - Exercises, Practice, Solution
- C# Sharp Basic Algorithm: Exercises, Practice, Solution
- Python Lambda - Exercises, Practice, Solution
- Python Pandas DataFrame: Exercises, Practice, Solution
- Conversion Tools
- JavaScript: HTML Form Validation