3

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?

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.

  • Why is it necessary for the WHERE clause to be in a subselect? – Wilson Oct 21 '17 at 17:03
  • 1
    @Wilson Yes, if you try putting the ROW_NUMBER() expression into the WHERE clause you will get ORA-30483: window functions are not allowed here. – MT0 Oct 21 '17 at 21:09
  • Would Oracle 12c's FETCH FIRST functionality be a third option? – Wilson Oct 25 '17 at 14:38
  • @Wilson No - that will just fetch a certain number of rows from the entire result set - not a certain number of rows for each partition of the result set. – MT0 Oct 25 '17 at 14:52
  • Fair point. ypercube's answer here might be of interest, although he figures it's not much of an improvement on row_number . – Wilson Oct 27 '17 at 1:40
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.

  • 1
    The only complication that I can think of would be around trying to filter on a date range. An end user might try to filter with 04/29/2016 as the end date and could be expecting ID 1 to appear. – Joe Obbish Mar 1 '17 at 1:19
  • Is there a typo in the aliases too (rn, a)? – Wilson Mar 1 '17 at 16:45
  • @Wilson : no, not a typo. maybe not the best style, but perfectly valid syntax. Table alias a and inline view alias a can be used this way without any conflict. – a1ex07 Mar 1 '17 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.