Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Salary (
  Id int,
  Name varchar(20),
  Salary int,
  DepartmentId int
);

insert into Salary values
( 1 , 'Petr' , 85000 , 1 ),
( 2 , 'Ivan' , 80000 , 2 ),
( 3 , 'Alex' , 60000 , 2 ),
( 4 , 'Den'  , 90000 , 1 ),
( 5 , 'Bob'  , 69000 , 1 ),
( 6 , 'Kir'  , 85000 , 1 ),
( 7 , 'Mike' , 76000 , 1 );


create table Department (
  DepartmentId int,
  DepartmentName varchar(20)
);

insert into Department values
(1, 'IT'),
(2, 'Sales');

CREATE TABLE



INSERT 0 7



CREATE TABLE



INSERT 0 2


with cte as (
  select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rn
  from Salary
)
select *
from cte


id
name
salary
departmentid
rn


4
Den
90000
1
1

1
Petr
85000
1
2

6
Kir
85000
1
2

7
Mike
76000
1
3

5
Bob
69000
1
4

2
Ivan
80000
2
1

3
Alex
60000
2
2



SELECT 7


with cte as (
  select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rn
  from Salary
)
select d.DepartmentName as Departement, Name as employee, Salary
from cte c
inner join Department d on d.DepartmentId = c.DepartmentId
where rn <= 3
order by c.departmentid, rn


departement
employee
salary


IT
Den
90000

IT
Petr
85000

IT
Kir
85000

IT
Mike
76000

Sales
Ivan
80000

Sales
Alex
60000



SELECT 6

id	name	salary	departmentid	rn
4	Den	90000	1	1
1	Petr	85000	1	2
6	Kir	85000	1	2
7	Mike	76000	1	3
5	Bob	69000	1	4
2	Ivan	80000	2	1
3	Alex	60000	2	2