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 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 from having time components as all your values will have a time component.

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.