Laurent Schneider

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

Blogroll, sql