I am working on a database query and I am facing issues creating the right query to get required output. Below is my create table query and insert statements.
create table DETAILS (dep_name varchar(50), emp_name varchar(50), salary int);
insert into DETAILS values ('marketing', 'ravi', 50000);
insert into DETAILS values ('marketing', 'suresh', 25000);
insert into DETAILS values ('marketing', 'mahesh', 60000);
insert into DETAILS values ('legal', 'raja', 40000);
insert into DETAILS values ('legal', 'gavi', 50000);
insert into DETAILS values ('legal', 'rana', 90000);
insert into DETAILS values ('legal', 'jyoti', 60000);
My requirement is to display only top 2 salaried employees from each department. Please see sample output below.
dep_name emp_name salary
legal rana 90000
legal jyoti 60000
marketing mahesh 60000
marketing ravi 50000
I have tried several queries but not able to crack this. Can somebody get me to frame the right query?
If you are using Oracle
then it can be done by dense_rank() over (partition by .. order by ..)
If your RDBMS is mysql
you cannot use dense_rank()
you have to emulate the concept.
See this link.
Try:
select
dep_name,
emp_name,
salary
from (
SELECT
@row_number:=CASE
WHEN @dep_name = dep_name THEN @row_number + 1
ELSE 1
END AS num,
@dep_name := dep_name as dep_name,
emp_name,
salary
FROM DETAILS ,(SELECT @dep_name := '', @row_number := 0) as t
ORDER BY dep_name, salary desc
) x
where num < 3
ORDER BY dep_name, salary desc;