last & first {aggregate function}
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument. APPLIES TO: Oracle 9i, Oracle 10g, Oracle 11g SYNTAX: aggregate_function(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name [ASC|DESC NULLS FIRST|LAST) OVER (PARTITION BY column_name) AGGREGATE EXAMPLE: The following example returns, within each department of the sample table 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
OVERVIEW: Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group. If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view. Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a ‘window’ and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
The Oracle SQL aggregate 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, the aggregate operates on the set with only one element.
emp.last_name,
emp.department_id,
emp.salary,
MIN(emp.salary) KEEP (DENSE_RANK FIRST ORDER BY emp.commission_pct) OVER
(PARTITION BY emp.department_id) min_salary,
MAX(emp.salary) KEEP (DENSE_RANK LAST ORDER BY emp.commission_pct) OVER
(PARTITION BY emp.department_id) max_salary
FROM
employees emp
WHERE
emp.department_id in (20, 30)
ORDER BY emp.department_id, emp.salary;
last_name
department_id
salary
min_salary
max_salary
Fay
20
6000
6000
13000
Hartstein
20
13000
6000
13000
Colmenares
30
2500
2500
11000
Himuro
30
2600
2500
11000
Tobias
30
2800
2500
11000
Baida
30
2900
2500
11000
Khoo
30
3100
2500
11000
Raphaely
30
11000
2500
11000