Querying employee salary along with maximum salary of his department

I found a very interesting question from one of the user of SQL group, his problem statement was

“I need to find each employee salary along with the max salary of his department with a single line query”

It is very easy to find using SQL ‘Over by with Partition By clause’

Lets demonstrate it with an example. Here I am creating Emp and Dept table and inserting few records in it.

create table Dept1

(dept_id int identity(1,1) primary key,

dname varchar(20))

Command(s) completed successfully.

create table Emp2

(emp_id int identity(1,1) primary key,

emp_name varchar(20),

dept_id int references Dept1(dept_id),

salary float)

Command(s) completed successfully.

insert into Dept1

select 'HR' union all

select 'IT' union all

select 'Account'

insert into Emp2

select 'Sudeepta', 1, 1000 union all

select 'Susmita', 1, 3000 union all

select 'Smruti', 3, 10000 union all

select 'Richa', 3, 8000 union all

select 'Sanjana', 1, 4000 union all

select 'Sweta', 2,4000 union all

select 'Deepa', 2,7000

Now since the data are there in the table, lets write query to find each employee salary along with the maximum salary for his department.

select e.emp_name, d.dname,e.salary, max(e.salary) over (partition by d.dept_id) MaxSalary from

Emp2 e inner join Dept1 d

on e.dept_id = d.dept_id

emp_name             dname                salary                 MaxSalary

-------------------- -------------------- ---------------------- ----------

Sudeepta             HR                   1000                   4000

Susmita              HR                   3000                   4000

Sanjana              HR                   4000                   4000

Sweta                IT                   4000                   7000

Deepa                IT                   7000                   7000

Smruti               Account              10000                  10000

Richa                Account              8000                   10000

(7 row(s) affected)

So here we can from the output we have salary for each employee along with the maximum salary of his department. We have simply used aggregated function max along with ‘over with partition by clause’ which is taking d.dept_id to partition the record to find the max salary of his department.

I can’t sleep …until I die…My passion SQL :)

Post Reference: Vikram Aristocratic Elfin Share