SELECT ID,
           MAX( "DATE" ) AS "DATE",
           MAX( CONDITION ) KEEP ( DENSE_RANK LAST ORDER BY "DATE" ) AS condition
    FROM   road_inspection
    GROUP BY id

or

    SELECT *
    FROM   (
      SELECT r.*,
             ROW_NUMBER() OVER ( PARTITION BY id ORDER BY "DATE" DESC ) AS rn
      FROM   road_inspection r
    )
    WHERE  rn = 1;

> The `DATE` column has both `DATE` and `DATE_TIME` values in it. Should I be worried about this causing complications when querying the column?

Oracle has no concept of a `DATE_TIME` data type. There is only `DATE` or `TIMESTAMP` and both of them have a `HH24:MI:SS` time component (`TIMESTAMP` also has fractional seconds). If a `DATE` value is set without a time component then oracle will default to midnight (`00:00:00`) of that day for the time component of the date.

Assuming your `DATE` column is actually stored as a `DATE` data type (and not as a `VARCHAR2`) then, no, there will not be any complications (with regards to selecting the rows with maximum values) from having time components as all your values will have a time component.