By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table employee(
EMPNO int,
ENAME varchar(10),
JOB varchar(20),
SAL int,
DEPTNO int);
insert into employee values (7698, 'BLAKE', 'MANAGER', 2850, 30)
1 rows affected
insert into employee values (7499, 'ALLEN', 'SALESMAN', 1600, 30);
1 rows affected
insert into employee values (7844, 'TURNER', 'SALESMAN', 1500, 30);
1 rows affected
insert into employee values (7839, 'KING', 'PRESIDENT', 5000, 10);
1 rows affected
insert into employee values (7782, 'CLARK', 'MANAGER', 2450, 10);
1 rows affected
insert into employee values (7934, 'MILLER', 'CLERK', 1300, 10);
1 rows affected
insert into employee values (7788, 'SCOTT', 'ANALYST', 3000, 20);
1 rows affected
insert into employee values (7369, 'SMITH', 'CLERK', 25000, 20);
1 rows affected
insert into employee values (7876, 'ADAMS', 'CLERK', 1100, 20);
1 rows affected
insert into employee values (7902, 'FORD', 'ANALYST', 3000, 20);
1 rows affected
select * from (
select e.*, row_number() over (partition by deptno order by sal desc, empno asc ) rn
from employee e
) where rn = 2;
| EMPNO | ENAME | JOB | SAL | DEPTNO | RN |
|---|---|---|---|---|---|
| 7782 | CLARK | MANAGER | 2450 | 10 | 2 |
| 7788 | SCOTT | ANALYST | 3000 | 20 | 2 |
| 7499 | ALLEN | SALESMAN | 1600 | 30 | 2 |