select  object_id,
        max(state) keep(last dense_rank order by state_date) state
  from  objects
  where state_date <= определенное_время
  GROUP BY object_id
/

with t as (
           select  o.*,
                   row_number() over(partition by object_id order by state_date desc) rn
             from  objects
             state_date <= определенное_время
          )
select  *
  from  t
  where rn = 1
/

max(state) keep(last dense_rank order by state_date) state

CREATE OR REPLACE
  FUNCTION LAST_STATE(
                      P_OBJECT_ID      NUMBER,
                      P_MAX_STATE_DATE DATE
                     )
    RETURN VARCHAR2
    IS
        V_STATE VARCHAR2(20);
    BEGIN
        SELECT  MAX(STATE)
          INTO  V_STATE
          FROM  OBJECTS
          WHERE OBJECT_ID  = P_OBJECT_ID
            AND STATE_DATE = P_MAX_STATE_DATE;
        RETURN V_STATE;
END;
/
SELECT  OBJECT_ID,
        LAST_STATE(OBJECT_ID,MAX(STATE_DATE)) STATE
  FROM  OBJECTS
  WHERE STATE_DATE <= определенное_время
  GROUP BY OBJECT_ID
/

SELECT  OBJECT_ID,
        SUBSTR(MAX(TO_CHAR(STATE_DATE,'YYYYMMDDHH24MISS') || STATE),15) STATE
  FROM  OBJECTS
  WHERE STATE_DATE <= определенное_время
  GROUP BY OBJECT_ID
/

SELECT * 
FROM( 
SELECT 
row_number() over(partition by obv.numopzn order by obv.dateizm desc) rn
, obv.numopzn numopzn

. . .

) 
WHERE rn=1; 

SELECT 
obv.numopzn numopzn

. . .

WHERE ROWID IN (
                SELECT SUBSTR(MAX(TO_CHAR(STATE_DATE,'YYYYMMDDHH24MISS') || ROWID),15) 
                  FROM  
                  WHERE STATE_DATE <= определенное_время
                  GROUP BY numopzn
               )

SELECT  *
  FROM  HR.EMPLOYEES
  WHERE ROWID IN (
                  SELECT  SUBSTR(MAX(TO_CHAR(HIRE_DATE,'YYYYMMDD') || ROWID),9)
                    FROM  HR.EMPLOYEES
                    WHERE HIRE_DATE <= DATE '1997-12-31'
                    GROUP BY DEPARTMENT_ID
                 )
  ORDER BY DEPARTMENT_ID
/

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17-SEP-87 AD_ASST          4400                       101            10
        202 Pat                  Fay                       PFAY                      603.123.6666         17-AUG-97 MK_REP           6000                       201            20
        116 Shelli               Baida                     SBAIDA                    515.127.4563         24-DEC-97 PU_CLERK         2900                       114            30
        203 Susan                Mavris                    SMAVRIS                   515.123.7777         07-JUN-94 HR_REP           6500                       101            40
        130 Mozhe                Atkinson                  MATKINSO                  650.124.6234         30-OCT-97 ST_CLERK         2800                       121            50
        105 David                Austin                    DAUSTIN                   590.423.4569         25-JUN-97 IT_PROG          4800                       103            60
        204 Hermann              Baer                      HBAER                     515.123.8888         07-JUN-94 PR_REP          10000                       101            70
        160 Louise               Doran                     LDORAN                    011.44.1345.629268   15-DEC-97 SA_REP           7500             .3        146            80
        102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13-JAN-93 AD_VP           17000                       100            90
        111 Ismael               Sciarra                   ISCIARRA                  515.124.4369         30-SEP-97 FI_ACCOUNT       7700                       108           100
        206 William              Gietz                     WGIETZ                    515.123.8181         07-JUN-94 AC_ACCOUNT       8300                       205           110

11 rows selected.

SQL>