The following gives you the most recent record per id

SELECT * FROM 
(
   SELECT a.* ,
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
)a
WHERE a.rn =1;

I'm not sure what you mean by complications when querying column of type DATE. 2010-01-01 is the same as midnight of Jan 1, 2010.

The following gives you the most recent record per id

SELECT * FROM 
(
   SELECT a.* ,
   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY date DESC) rn
   FROM USER1.ROAD_INSPECTION a
)a
WHERE a.rn =1;

I'm not sure what you mean by complications when querying column of type DATE. 2010-01-01 is the same as midnight of Jan 1, 2010.

Update. Fixed missed FROM clause.