Oracle PL/SQL-related query implementation

Recently, I have seen two query syntaxes that are not commonly used on the Internet for sorting queries. For the usage of analysis functions, remember, authenticate, and explain;

How can I query the maximum and secondary values of each column in a table?

WITH tt        AS (SELECT CASE WHEN col1 IN (1, 2) THEN empno ELSE NULL END empno,                   CASE WHEN col2 IN (1, 2) THEN ename ELSE NULL END ename,                   CASE WHEN col3 IN (1, 2) THEN job ELSE NULL END job,                   CASE WHEN col4 IN (1, 2) THEN mgr ELSE NULL END mgr,                   CASE WHEN col5 IN (1, 2) THEN hiredate ELSE NULL END                      hiredate,                   CASE WHEN col6 IN (1, 2) THEN sal ELSE NULL END sal,                   CASE WHEN col7 IN (1, 2) THEN comm ELSE NULL END comm,                   CASE WHEN col8 IN (1, 2) THEN deptno ELSE NULL END deptno              FROM (SELECT empno,                           ROW_NUMBER () OVER (ORDER BY empno DESC) col1,                           ename,                           ROW_NUMBER () OVER (ORDER BY ename DESC) col2,                           job,                           ROW_NUMBER () OVER (ORDER BY job DESC) col3,                           mgr,                           ROW_NUMBER () OVER (ORDER BY mgr DESC) col4,                           hiredate,                           ROW_NUMBER () OVER (ORDER BY hiredate DESC) col5,                           sal,                           ROW_NUMBER () OVER (ORDER BY sal DESC) col6,                           comm,                           ROW_NUMBER () OVER (ORDER BY comm DESC) col7,                           deptno,                           ROW_NUMBER () OVER (ORDER BY deptno DESC) col8                      FROM emp))SELECT MAX (empno) empno,       MAX (ename) ename,       MAX (job) job,       MAX (mgr) mgr,       MAX (hiredate) hiredate,       MAX (sal) sal,       MAX (comm) comm,       MAX (deptno) deptno  FROM ttUNION ALLSELECT MIN (empno) empno,       MIN (ename) ename,       MIN (job) job,       MIN (mgr) mgr,       MIN (hiredate) hiredate,       MIN (sal) sal,       MIN (comm) comm,       MIN (deptno) deptno  FROM tt

Perform the following operations on the data in a table: group the data first, sort the data in the group, and then display the first two rows in each group.

select * from (select d.empno,       d.deptno,       sum(sal),       rank() over(partition by deptno order by sum(sal) desc) rank  from scott.emp d group by d.empno, d.deptno) where rank<3