LAST
--------
FIRST and
The following example returns, within each department of the sample table
--------
FIRST and
LAST
are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST
or LAST
with respect to a given sorting specification. If only one row ranks as FIRST
or LAST
, then the aggregate operates on the set with only one element.The following example returns, within each department of the sample table
hr.employees
, the minimum salary among the employees who make the lowest commission and the maximum salary among the employees who make the highest commission:SELECT department_id, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best" FROM employees GROUP BY department_id ORDER BY department_id; DEPARTMENT_ID Worst Best ------------- ---------- ---------- 10 4400 4400 20 6000 13000 30 2500 11000 40 6500 6500 50 2100 8200 60 4200 9000 70 10000 10000 80 6100 14000 90 17000 24000 100 6900 12008 110 8300 12008 7000 7000The next example makes the same calculation as the previous example but returns the result for each employee within the department:
SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) OVER (PARTITION BY department_id) "Best" FROM employees ORDER BY department_id, salary, last_name; LAST_NAME DEPARTMENT_ID SALARY Worst Best ------------------- ------------- ---------- ---------- ---------- Whalen 10 4400 4400 4400 Fay 20 6000 6000 13000 Hartstein 20 13000 6000 13000 . . . Gietz 110 8300 8300 12008 Higgins 110 12008 8300 12008 Grant 7000 7000 7000
No comments:
Post a Comment