ANY_VALUE and FIRST/LAST (KEEP)
ANY_VALUE and FIRST/LAST (KEEP) The FIRST and LAST (a.k.a. “KEEP”) aggregate functions are very useful when you want to order a row set by one column and return the value of another column from the first or last row in the ordered set. I wrote about these functions in the past; for example here and here . To make sure the result of the FIRST (LAST) function is deterministic, we have to define a tie-breaker for the case that multiple rows have the same first (last) value. The tie-breaker is an aggregate function that is applied on the column we want to return. For example, the following query returns for each department the FIRST_NAME of the employee with the lowest SALARY. If there are multiple employees with the lowest salary in the same department, the query returns the “minimum” first_name – so here the MIN function is the tie-breaker. In this example the lowest salary in department 90 is 17000, and both Neena and Lex have this salary....