Selecting minimum value in top grouping only

I have a query built in Oracle 10 which produces a set of data like this:

EMP_ID  WORK_DT  TIME_TYPE
---------- --------- ---------
     12345 19-DEC-14        10
     12345 18-DEC-14        10
     12345 17-DEC-14        10
     12345 16-DEC-14        20
     12345 15-DEC-14        20
     12345 14-DEC-14        10
     12345 13-DEC-14        10
     54321 19-DEC-14        10
     54321 18-DEC-14        20
[...]

and I'm looking for output like this, only showing the most recent time_type for each employee with the applicable START_DT:

EMP_ID  START_DT   END_DT  TIME_TYPE 
---------- --------- --------- --------- 
     12345 17-DEC-14 19-DEC-14        10
     54321 19-DEC-14 19-DEC-14        10

The START_DT has been troublesome for me because the same TIME_TYPE can exist in multiple blocks in the input.

My background is more with other RDBMSs than Oracle. I've spent the last few days searching here and experimenting with Oracle's analytical, aggregate, and subquery functions to get these results. I feel like I'm close but I haven't had much luck.

I'm trying to keep this as an efficient set-based solution rather than resorting to a cursor or function.

Any help is very appreciated!

Not sure about the efficient part, but the following does the trick. You'll want (emp_id, work_dt) indexed at least, having the time_type in there should not hurt either.

Assuming this table:

create table times(emp_id number, work_dt date, time_type number);

You can run this: (SQL Fiddle)

-- get the last work date
with last_dt as (
  select emp_id, max(work_dt) last_dt
  from times
  group by emp_id
),
-- get the last time type
last_rec as (
  select l.emp_id, l.last_dt, t.time_type
  from last_dt l
  join times t on l.emp_id = t.emp_id
              and l.last_dt = t.work_dt
),
-- get the last record for an employee
-- that isn't of the last time type
-- last_ot will be null for employees
-- who always do the same thing
last_other_type as (
  select l.emp_id
  , min(l.time_type) time_type -- "constant" per employee
  , min(l.last_dt) last_dt     -- same
  , max(t.work_dt) last_ot
  from last_rec l
  left join times t on l.emp_id = t.emp_id
              and l.time_type != t.time_type
  group by l.emp_id
)
select f.emp_id
, case when f.last_ot is null
    then (select min(work_dt) from times where emp_id = f.emp_id)
    else (select min(work_dt) from times where emp_id = f.emp_id
                                           and time_type = f.time_type
                                           and work_dt > f.last_ot)
                                           -- use >= if there can be more than
                                           -- one record per day
  end first_dt
, f.last_dt
, f.time_type
from last_other_type f

The idea is to get the latest time and type for each employee, then find the latest date where they worked on something different.

• If such a date is found, return the first time they did the last type of time of work that is later than that.

• If not, then the employee only did one type of work, so pick the very first date recorded.

For Oracle 12c, there's the match_recognize tool you can use to process this sort of "custom" grouping.

select emp_id, nr
, time_type
, first_dt
, last_dt
from times
match_recognize (
  partition by emp_id order by work_dt desc
  measures match_number() as nr
  , first(a.work_dt)   as first_dt
  , first(a.time_type) as time_type
  , last(a.work_dt)    as last_dt
  pattern (a+)
  define a as (a.time_type = first(a.time_type))
)
-- where nr = 1

This returns:

EMP_ID         NR  TIME_TYPE FIRST_DT  LAST_DT
---------- ---------- ---------- --------- ---------
     12345          1         10 19-DEC-14 17-DEC-14
     12345          2         20 16-DEC-14 15-DEC-14
     12345          3         10 14-DEC-14 13-DEC-14
     54321          1         10 19-DEC-14 19-DEC-14
     54321          2         20 18-DEC-14 18-DEC-14

Filter on nr == 1 and you're done.

StackExchange Database Administrators Q#86928, answer score: 2