hr@XE:11g2> column salary format 999999;
hr@XE:11g2> column tenure format 999999;
hr@XE:11g2> column deptjob format a15;
hr@XE:11g2> column sprank format 999.9;
hr@XE:11g2> column tprank format 999.9;
hr@XE:11g2> column prskew format 999.9;
hr@XE:11g2> r
1 SELECT
2 department_id||job_id AS deptjob,
3 last_name,
4 salary,
5 sysdate - hire_date AS tenure,
6 100*PERCENT_RANK() OVER
7 (PARTITION BY department_id||job_id ORDER BY salary ASC) AS sprank,
8 100*PERCENT_RANK() OVER
9 (PARTITION BY department_id||job_id ORDER BY hire_date DESC) AS tprank,
10 100*PERCENT_RANK() OVER
11 (PARTITION BY department_id||job_id ORDER BY hire_date DESC)
12 - 100*PERCENT_RANK() OVER
13 (PARTITION BY department_id||job_id ORDER BY salary ASC) AS prskew
14 FROM employees
15 WHERE department_id||job_id IN
16 (SELECT deptjob FROM
17 (SELECT COUNT(*), department_id||job_id AS deptjob
18 FROM employees
19 GROUP BY department_id||job_id
20 HAVING COUNT(*) > 3
21 )
22 )
23 ORDER BY 1 ASC, 7 DESC
24 /
DEPTJOB LAST_NAME SALARY TENURE SPRANK TPRANK PRSKEW
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~
100FI_ACCOUNT Sciarra 91661 1673 25.0 50.0 25.0
100FI_ACCOUNT Popp 82138 875 .0 .0 .0
100FI_ACCOUNT Faviet 107136 2814 100.0 100.0 .0
100FI_ACCOUNT Chen 97613 1675 75.0 75.0 .0
100FI_ACCOUNT Urman 92851 1515 50.0 25.0 -25.0
30PU_CLERK Tobias 27776 1741 50.0 75.0 25.0
30PU_CLERK Khoo 30752 2539 100.0 100.0 .0
30PU_CLERK Colmenares 24800 994 .0 .0 .0
30PU_CLERK Himuro 25792 1262 25.0 25.0 .0
30PU_CLERK Baida 28768 1588 75.0 50.0 -25.0
50SH_CLERK Sullivan 24800 1044 .0 15.8 15.8
50SH_CLERK Feeney 29760 1438 36.8 52.6 15.8
50SH_CLERK Fleaur 30752 1527 47.4 63.2 15.8
50SH_CLERK Perkins 24800 863 .0 10.5 10.5
50SH_CLERK Taylor 31744 1557 57.9 68.4 10.5
50SH_CLERK Walsh 30752 1467 47.4 57.9 10.5
50SH_CLERK Jones 27776 1140 21.1 26.3 5.3
50SH_CLERK Bell 39680 2277 89.5 94.7 5.3
50SH_CLERK Gates 28768 1389 31.6 36.8 5.3
50SH_CLERK OConnell 25792 1044 10.5 15.8 5.3
50SH_CLERK Sarchand 41664 2285 100.0 100.0 .0
50SH_CLERK Dilly 35712 1721 73.7 73.7 .0
50SH_CLERK Chung 37696 1781 78.9 78.9 .0
50SH_CLERK Everett 38688 1884 84.2 84.2 .0
50SH_CLERK Cabrio 29760 1178 36.8 31.6 -5.3
50SH_CLERK Grant 25792 838 10.5 5.3 -5.3
50SH_CLERK Bull 40672 1895 94.7 89.5 -5.3
50SH_CLERK McCain 31744 1399 57.9 42.1 -15.8
50SH_CLERK Dellinger 33728 1406 68.4 47.4 -21.1
50SH_CLERK Geoni 27776 817 21.1 .0 -21.1
50ST_CLERK Marlow 24800 1899 26.3 78.9 52.6
50ST_CLERK Davies 30752 1917 68.4 84.2 15.8
50ST_CLERK Olson 20832 1116 .0 15.8 15.8
50ST_CLERK Patel 24800 1485 26.3 42.1 15.8
50ST_CLERK Vargas 24800 1391 26.3 36.8 10.5
50ST_CLERK Landry 23808 1202 15.8 21.1 5.3
50ST_CLERK Matos 25792 1507 42.1 47.4 5.3
50ST_CLERK Seo 26784 1538 47.4 52.6 5.3
50ST_CLERK Mallin 32736 2146 84.2 89.5 5.3
50ST_CLERK Philtanker 21824 814 5.3 5.3 .0
50ST_CLERK Atkinson 27776 1643 57.9 57.9 .0
50ST_CLERK Nayer 31744 1749 73.7 73.7 .0
50ST_CLERK Ladwig 35712 2482 100.0 100.0 .0
50ST_CLERK Rajs 34720 2387 94.7 94.7 .0
50ST_CLERK Markle 21824 783 5.3 .0 -5.3
50ST_CLERK Gee 23808 870 15.8 10.5 -5.3
50ST_CLERK Stiles 31744 1647 73.7 63.2 -10.5
DEPTJOB LAST_NAME SALARY TENURE SPRANK TPRANK PRSKEW
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~ ~~~~~~
50ST_CLERK Bissot 32736 1714 84.2 68.4 -15.8
50ST_CLERK Mikkilineni 26784 1310 47.4 26.3 -21.1
50ST_CLERK Rogers 28768 1343 63.2 31.6 -31.6
50ST_MAN Kaufling 78368 2556 50.0 100.0 50.0
50ST_MAN Weiss 79360 2112 75.0 75.0 .0
50ST_MAN Mourgos 57536 896 .0 .0 .0
50ST_MAN Vollman 64480 1663 25.0 25.0 .0
50ST_MAN Fripp 81344 1846 100.0 50.0 -50.0
60IT_PROG Austin 76186 1770 25.0 100.0 75.0
60IT_PROG Pataballa 76186 1545 25.0 50.0 25.0
60IT_PROG Lorentz 66662 1178 .0 25.0 25.0
60IT_PROG Hunold 142848 1578 100.0 75.0 -25.0
60IT_PROG Ernst 95232 1075 75.0 .0 -75.0
80SA_MAN Partners 133920 1941 75.0 75.0 .0
80SA_MAN Errazuriz 119040 1877 50.0 50.0 .0
80SA_MAN Cambrault 109120 928 25.0 25.0 .0
80SA_MAN Zlotkey 104160 822 .0 .0 .0
80SA_MAN Russell 138880 2037 100.0 100.0 .0
80SA_REP Smith 79360 1877 42.9 82.1 39.3
80SA_REP McEwen 89280 2098 60.7 89.3 28.6
80SA_REP Sully 94240 2248 67.9 96.4 28.6
80SA_REP Doran 74400 1597 35.7 60.7 25.0
80SA_REP Sewall 69440 1274 17.9 39.3 21.4
80SA_REP Hutton 87296 1868 57.1 75.0 17.9
80SA_REP King 99200 2282 82.1 100.0 17.9
80SA_REP Johnson 61504 847 3.6 17.9 14.3
80SA_REP Hall 89280 1714 60.7 67.9 7.1
80SA_REP Bernstein 94240 1863 67.9 71.4 3.6
80SA_REP Tucker 99200 1916 82.1 85.7 3.6
80SA_REP Olsen 79360 1492 42.9 46.4 3.6
80SA_REP Tuvault 69440 889 17.9 21.4 3.6
80SA_REP Smith 73408 1162 32.1 32.1 .0
80SA_REP Cambrault 74400 1238 35.7 35.7 .0
80SA_REP Kumar 60512 739 .0 .0 .0
80SA_REP Taylor 85312 1498 53.6 50.0 -3.6
80SA_REP Bates 72416 1133 28.6 25.0 -3.6
80SA_REP Ande 63488 767 10.7 7.1 -3.6
80SA_REP Banda 61504 739 3.6 .0 -3.6
80SA_REP Abel 109120 2180 96.4 92.9 -3.6
80SA_REP Lee 67456 797 14.3 10.7 -3.6
80SA_REP Livingston 83328 1468 50.0 42.9 -7.1
80SA_REP Marvins 71424 827 25.0 14.3 -10.7
80SA_REP Fox 95232 1557 78.6 57.1 -21.4
80SA_REP Ozer 114080 1876 100.0 78.6 -21.4
80SA_REP Bloom 99200 1499 82.1 53.6 -28.6
80SA_REP Vishney 104160 1631 92.9 64.3 -28.6
80SA_REP Greene 94240 1138 67.9 28.6 -39.3
94 rows selected.