Oracle Select Max Date on Multiple records

Question

I've got the following SELECT statement, and based on what I've seen here: SQL Select Max Date with Multiple records I've got my example set up the same way. I'm on Oracle 11g. Instead of returning one record for each asset_tag, it's returning multiples. Not as many records as in the source table, but more than (I think) it should be. If I run the inner SELECT statement, it also returns the correct set of records (1 per asset_tag), which really has me stumped.

SELECT 
outside.asset_tag,
outside.description, 
outside.asset_type, 
outside.asset_group, 
outside.status_code, 
outside.license_no, 
outside.rentable_yn, 
outside.manufacture_code, 
outside.model, 
outside.manufacture_vin, 
outside.vehicle_yr, 
outside.meter_id, 
outside.mtr_uom, 
outside.mtr_reading, 
outside.last_read_date
FROM mp_vehicle_asset_profile outside
RIGHT OUTER JOIN
  (
  SELECT asset_tag, max(last_read_date) as last_read_date
  FROM mp_vehicle_asset_profile
  group by asset_tag
  ) inside
ON outside.last_read_date=inside.last_read_date

Any suggestions?

score 1 · Answer 1 · answered May 25 '12 at 21:41

1

Try with analytical functions:

SELECT  outside.asset_tag,
        outside.description, 
        outside.asset_type, 
        outside.asset_group, 
        outside.status_code, 
        outside.license_no, 
        outside.rentable_yn, 
        outside.manufacture_code, 
        outside.model, 
        outside.manufacture_vin, 
        outside.vehicle_yr, 
        outside.meter_id, 
        outside.mtr_uom, 
        outside.mtr_reading, 
        outside.last_read_date
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY asset_tag ORDER BY last_read_date DESC) Corr
        FROM mp_vehicle_asset_profile) outside
WHERE Corr = 1

answered May 25 '12 at 21:41

Lamak

69,480
12
108
116

Out of curiousity, what does "Corr" stand for? – Gordon Linoff May 26 '12 at 01:56
Corr is the alias for the item in the row, so the row number returns a number, Lamak has given it an alias and used it to retrieve row number 1. – Robbie Dec 18 '12 at 17:46

Brian Camire · Accepted Answer · 2012-05-30T00:06:49.463

I think you need to add...

AND outside.asset_tag=inside.asset_tag

...to the criteria in your ON list.

Also a RIGHT OUTER JOIN is not needed. An INNER JOIN will give the same results (and may be more efficicient), since there will be cannot be be combinations of asset_tag and last_read_date in the subquery that do not exist in mp_vehicle_asset_profile.

Even then, the query may return more than one row per asset tag if there are "ties" -- that is, multiple rows with the same last_read_date. In contrast, @Lamak's analytic-based answer will arbitrarily pick exactly one row this situation.

Your comment suggests that you want to break ties by picking the row with highest mtr_reading for the last_read_date.

You could modify @Lamak's analyic-based answer to do this by changing the ORDER BY in the OVER clause to:

ORDER BY last_read_date DESC, mtr_reading DESC

If there are still ties (that is, multiple rows with the same asset_tag, last_read_date, and mtr_reading), the query will again abritrarily pick exactly one row.

You could modify my aggregate-based answer to break ties using highest mtr_reading as follows:

SELECT  
    outside.asset_tag, 
    outside.description,
    outside.asset_type,
    outside.asset_group,
    outside.status_code,
    outside.license_no,
    outside.rentable_yn,
    outside.manufacture_code,
    outside.model,
    outside.manufacture_vin,
    outside.vehicle_yr,
    outside.meter_id,
    outside.mtr_uom,
    outside.mtr_reading,
    outside.last_read_date 
FROM 
    mp_vehicle_asset_profile outside 
    INNER JOIN 
    ( 
        SELECT
            asset_tag, 
            MAX(last_read_date) AS last_read_date,
            MAX(mtr_reading) KEEP (DENSE_RANK FIRST ORDER BY last_read_date DESC) AS mtr_reading
        FROM
            mp_vehicle_asset_profile 
        GROUP BY
            asset_tag 
    ) inside 
    ON 
        outside.asset_tag = inside.asset_tag
        AND
        outside.last_read_date = inside.last_read_date
        AND
        outside.mtr_reading = inside.mtr_reading

If there are still ties (that is, multiple rows with the same asset_tag, last_read_date, and mtr_reading), the query may again return more than one row.

One other way that the analytic- and aggregate-based answers differ is in their treatment of nulls. If any of asset_tag, last_read_date, or mtr_reading are null, the analytic-based answer will return related rows, but the aggregate-based one will not (because the equality conditions in the join do not evaluate to TRUE when a null is involved.

This is what I needed. Took care of it in one fix. I wasn't thinking about multiple records with the same last reading dates of other cars, so I didn't think to add that in. The last_read_date I thought was a DATETIME field, but I was wrong. Thanks! — danielw-msu, May 29 '12 at 14:06
Just to (hopefully) be clear... Even if you add the condition to the `ON` list and use and `INNER JOIN` as I suggested, the query may still return more than one row per asset tag if there are "ties" on `last_read_date`. If this is not what you want, how do what to break the ties? @Lamak's analytic-based answer will arbitrarily pick one, or can be modified (by adding expressions to the `ORDER BY` list) to break ties in other ways. There are also ways to modify my aggregate-based answer to do the same. Post back if interested. — Brian Camire, May 29 '12 at 16:39
In this data set, I'm not too worried about the tie condition, but let's explore it. If a tie comes along as far as date I would want to pick the highest of any `mtr_reading` that exists on that date. Reasoning is that the car being driven could have been driven in the morning, and then again in the evening before the report is run. Would we add an `order by ... mtr_reading` to the outer query, resulting in those being sorted? or do we need to tweak the inner query? — danielw-msu, May 29 '12 at 20:38

Oracle Select Max Date on Multiple records

2 Answers2

Linked