keep dense_rank with multiple column

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

insert into t values (
10,'Jo','Smith',date '2001-01-01');

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;

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

3 thoughts on “keep dense_rank with multiple column

  1. DJ

    How about using rowid?
    select deptno,
    max(firstname) keep (dense_rank last
    order by hiredate,rowid) firstname,
    max(lastname) keep (dense_rank last
    order by hiredate,rowid) lastname,
    max(hiredate) hiredate
    from t
    group by deptno;

Comments are closed.