CODEWITHSUNDEEP
sql-server
Aryan Sena
Aryan Sena

Reputation: 219

How to get department wise highest salary and highest salary holder Employee name,consider 3 tables are joined using keys

How to get department wise highest salary and highest salary holder Employee name,consider 3 tables are joined using keys io/-

emptable,        saltable,depttable
id name dept_id  id sal dept_id dept_name
1 ram   10       1 100   10      xyz
2 sham  10       2 500   20      abc
3 jadu  20       3 900   30      por
4 john  20       4 999   40      stu
5 Madhu 30       5 300   50      xxx

o/p-

name,sal,dept_name
sham 500 abc
john 999 stu
Madhu300 xxx

Upvotes: 0

Views: 453

Answers (2)

Akshay Rane
Akshay Rane

Reputation: 423

SELECT DISTINCT
            MAX(amount) OVER (PARTITION BY d.id ORDER BY s.amount DESC) AS sal, 
            FIRST_VALUE(e.name) OVER (PARTITION BY d.id ORDER BY s.amount DESC) AS emp,
            d.name as dept
FROM        emptable e
INNER JOIN  depttable d
    ON      e.dept_id = d.id
INNER JOIN  saltable s
    ON      s.id = e.id

I made some assumptions for column names in your tables. You can find my schema script here.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can JOIN all the table and then use window function row_number to choose top two records per department:

select *
from (
    select t.*,
        row_number() over (
            partition by dept_id order by salary desc
            ) rn
    from (
        select e.*,
            s.salaray,
            d.dept_name
        from emptable e
        join saltable s on e.emp_id = s.emp_id
        join depttable d on d.dept_id = e.dept_id
        ) t
    ) t
where rn <= 2;

Your table are missing the column names. So, I assumed the names (hopefully correctly)

Upvotes: 0

Related Questions