Untitled

select  (
select CATEG from
(select CATEG  from SHIFTS where TABNOM = STATE.TABNOM  order by TABNOM, DATEPR desc)
where ROWNUM = 1
)
from STATE

select (
    select distinct First_Value(h.Categ) over (partition by h.Tabnom order by h.Datepr desc)
    from SHIFTS h
    where h.TabNom = s.Tabnom)
from State s

select  (
    select LSHIFT.CAT2 as CAT1 from
       (select SHIFTS.CATEG as CAT2
           from SHIFTS where TABNOM = STATE.TABNOM  order by
           TABNOM, DATEPR desc LIMIT 1) as LSHIFT
       where ROWNUM = 1
) as CATEG from STATE