Select most recent inspection

Question

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?

Answer 1

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.

Answer 2

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.