SQLize | PHPize | SQLtest

CREATE TABLE emp ( empno integer, ename character(100), job character(100), mgr integer, hiredate date, sal integer, comm integer, deptno integer ); -- -- Name: emp_bonus; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE emp_bonus ( empno integer, received date, type integer ); insert into emp VALUES (7369, 'SMITH', 'CLERK ', 7902, '17-DEC-1980', 800, null, 20); insert into emp VALUES (7499, 'ALLEN', 'SALESMAN ', 7698, '20-FEB-1981', 1600, 300, 30); insert into emp VALUES (7521, 'WARD', 'SALESMAN ', 7698, '22-FEB-1981', 1250, 500, 30); insert into emp VALUES (7566, 'JONES', 'MANAGER ', 7839, '02-APR-1981', 2975, null, 20); insert into emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-1981', 1250, 1400, 30); insert into emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-1981', 2850, null, 30); insert into emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-1981', 2450, null, 10); insert into emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-1982', 3000, null, 20); insert into emp VALUES (7839, 'KING', 'PRESIDENT', null, '17-NOV-1981', 5000, null, 10); insert into emp VALUES (7844, 'TURNER ', 'SALESMAN', 7698, '08-SEP-1981', 1500, 0, 30); insert into emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '12-JAN-1983', 1100, null, 20); insert into emp VALUES (7900, 'JAMES', 'CLERK', 7698, '03-DEC-1981', 950, null, 30); insert into emp VALUES (7902, 'FORD', 'ANALYST', 7566, '03-DEC-1981 ', 3000, null, 20); insert into emp VALUES (7934, 'MILLER', 'CLERK', 7782, '23-JAN-1982', 1300, null, 10); INSERT INTO EMP_BONUS (EMPNO,RECEIVED,TYPE) VALUES ( 7934, '17-MAR-2023', 1 ); INSERT INTO EMP_BONUS (EMPNO,RECEIVED,TYPE) VALUES ( 7934, '15-FEB-2023', 2 ); INSERT INTO EMP_BONUS (EMPNO,RECEIVED,TYPE) VALUES ( 7839, '15-FEB-2023', 3 ); INSERT INTO EMP_BONUS (EMPNO,RECEIVED,TYPE) VALUES ( 7782, '15-FEB-2023', 1 ); select max(case grp when 1 then rpad(ename,6) || ' ('|| sal ||')' end) top_3, max(case grp when 2 then rpad(ename,6) || ' ('|| sal ||')' end) next_3, max(case grp when 3 then rpad(ename,6) || ' ('|| sal ||')' end) rest from ( select ename, sal, rnk, case when rnk <= 3 then 1 when rnk <= 6 then 2 else 3 end grp, row_number()over (partition by case when rnk <= 3 then 1 when rnk <= 6 then 2 else 3 end order by sal desc, ename ) grp_rnk from( select ename, sal, dense_rank()over(order by sal desc) rnk from emp ) x ) y group by grp_rnk