I just re-read the documentation for the FIRST / LAST analytic/aggregate functions, and for the first time I noticed the following paragraph:
These functions take as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FIRST.html#GUID-85AB9246-0E0A-44A1-A7E6-4E57502E9238
Is that just a documentation bug, or is the function really not meant to be used with VARCHAR2, DATE, etc. data types? They do work just fine (and as expected) with those data types, but the documentation says they shouldn't. Is this something widely known? I have used FIRST / LAST in many of my answers, perhaps 90% of those answers would be wrong if the documentation is correct.
To show a few trivial examples:
Suppose based on the EMP table in the SCOTT schema, we want to find the NAME of the lowest-paid employee in each JOB description, and in the case of ties, take the first name in lexicographic order.
select job, min(ename) keep (dense_rank first order by sal) as lowest_paid_employee
from scott.emp
group by job
;
JOB LOWEST_PAID_EMPLOYEE
--------- --------------------
ANALYST FORD
CLERK SMITH
MANAGER CLARK
PRESIDENT KING
SALESMAN MARTIN
OK - perhaps VARCHAR2 can be converted implicitly to NUMBER (indeed it can), and Big Brother Oracle, instead of throwing an error when it can't implicitly convert to NUMBER, simply compares strings as strings.
But that doesn't explain why it doesn't convert the strings to numbers when it can do so:
with
t (grp, str) as (
select 101, '9' from dual union all
select 101, '10' from dual
)
select min(str) keep (dense_rank first order by null) as result
from t
group by grp
;
RESULT
------
10
Clearly here STR are compared as strings, not as numbers, even though they could be converted to NUMBER. And the return data type is VARCHAR2, not NUMBER as the documentation says.
And, we can take MIN(HIREDATE) KEEP (DENSE_RANK FIRST/LAST ....) - the argument can be of DATE data type, which can't be converted to a numeric data type.
So, is this a documented documentation bug? Or am I just abusing the function whenever I use it for strings, dates, etc.?
For what it's worth:
- The documentation has been saying the same thing since at least version 10.1 (but the function worked as I have been using it, since at least version 11.1);
- LISTAGG doesn't work as the aggregate part of FIRST/LAST. This would be consistent with the documentation; but there are other places where LISTAGG is not valid, which have nothing to do with "can be implicitly converted to numeric data type".