keep dense_rank with multiple column

create table t(
deptno number,
firstname varchar2(10),
lastname varchar2(10),
hiredate date);

insert into t values (
10,'Jack','River',date '2002-02-02');


to get the latest hiredate per department
select deptno,
max(hiredate) hiredate
from t
group by deptno;

DEPTNO HIREDATE
---------- ---------
10 02-FEB-02


if you want to get the name of the employee at that date, you could by mistake believe the following works

select deptno,
max(firstname) keep (dense_rank last
order by hiredate) firstname,
max(lastname) keep (dense_rank last
order by hiredate) lastname,
max(hiredate) hiredate
from t
group by deptno;

DEPTNO FIRSTNAME LASTNAME HIREDATE
---------- ---------- ---------- ---------
10 Jack River 02-FEB-02


This will produce wrong result if hiredate is not unique

insert into t values (10,'Bob','Zhong', date '2002-02-02');
select deptno,
max(firstname) keep (dense_rank last
order by hiredate) firstname,
max(lastname) keep (dense_rank last
order by hiredate) lastname,
max(hiredate) hiredate
from t
group by deptno;

DEPTNO FIRSTNAME LASTNAME HIREDATE
---------- ---------- ---------- ---------
10 Jack Zhong 02-FEB-02


of course there is Jack Zhong.

To get a consistent record, it is possible to add all the columns in the order by

select deptno,
max(firstname) keep (dense_rank last
order by hiredate,firstname,lastname) firstname,
max(lastname) keep (dense_rank last
order by hiredate,firstname,lastname) lastname,
max(hiredate) hiredate
from t
group by deptno;