Select most recent inspection I have a ROAD_INSPECTION table:

+----+------------------------+-----------+
| ID |          DATE          | CONDITION |
+----+------------------------+-----------+
|  1 | 01/01/2009             |        20 |
|  1 | 05/01/2013             |        16 |
|  1 | 04/29/2016 10:02:52 AM |        15 |
+----+------------------------+-----------+
|  2 | 01/01/2009             |         8 |
|  2 | 06/06/2012 9:55:13 AM  |         8 |
|  2 | 04/28/2015             |        11 |
+----+------------------------+-----------+
|  3 | 06/11/2012             |        10 |
|  3 | 04/21/2015             |        19 |
+----+------------------------+-----------+




What is the most efficient way to select the most recent inspection? The query would need to include the CONDITION column, despite the fact that it wouldn't group by cleanly:

+----+------------------------+-----------+
| ID |          DATE          | CONDITION |
+----+------------------------+-----------+
|  1 | 04/29/2016 10:02:52 AM |        15 |
+----+------------------------+-----------+
|  2 | 04/28/2015             |        11 |
+----+------------------------+-----------+
|  3 | 04/21/2015             |        19 |
+----+------------------------+-----------+




For bonus points: The DATE column has both DATE and DATE_TIME values in it. Should I be worried about this causing complications when querying the column?
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.