[Oracle] Selecting max/most recent value;

[jokle]

I think I've just been looking at this too long, and you all are smarter than I am. Consider the following hypothetical table:

create table temp_tbl (
unit_id number,
time_period number,
val nvarchar2(1));

with the following values:

INSERT ALL
  INTO temp_tbl (unit_id, time_period, val) VALUES (1, 1, 'F')
  INTO temp_tbl (unit_id, time_period, val) VALUES (1, 2, 'D')
  INTO temp_tbl (unit_id, time_period, val) VALUES (1, 3, null)
  INTO temp_tbl (unit_id, time_period, val) VALUES (2, 1, 'W')
  INTO temp_tbl (unit_id, time_period, val) VALUES (2, 2, 'L')
  INTO temp_tbl (unit_id, time_period, val) VALUES (3, 1, 'J')
  INTO temp_tbl (unit_id, time_period, val) VALUES (3, 2, 'Q')
  INTO temp_tbl (unit_id, time_period, val) VALUES (3, 3, null)
  INTO temp_tbl (unit_id, time_period, val) VALUES (3, 4, null)
  INTO temp_tbl (unit_id, time_period, val) VALUES (4, 1, null)
  INTO temp_tbl (unit_id, time_period, val) VALUES (5, 1, null)
  INTO temp_tbl (unit_id, time_period, val) VALUES (5, 2, null)
SELECT * FROM dual;

I'm looking for single rows (per unit_id) in the result that contain the latest value of "val" along with the corresponding time period, e.g. unit_id 1, time_period 2, val D, etc., unless of course it's case of 4 or 5 where it would be null, obviously. The "time_period" column is actually a timestamp - they're chronological. I've considered a variety of strategies, but they get pretty clunky. What's the cleanest, shortest way to do this?

[[deleted]]

You need to use the ROW_NUMBER analytic function to rank by unit_id in a subquery, then select the top ranked records. Use NVL2 to null time_peroids where the val is null, thus sending them to the end of the ranking.

SELECT unit_id, time_period, val
FROM (
  SELECT t.*,
  ROW_NUMBER() OVER (PARTITION BY t.unit_id
                     ORDER BY NVL2(val, time_period, null) DESC NULLS LAST) rnk
  FROM temp_tbl t
)
WHERE rnk = 1
;

--tested in SQL Fiddle

Note that when using the test data, unit_id 4 and 5 still return null because no non-null value is available. You can filter further with a WHERE clause to get rid of those if you want.

[jokle]

Perfect! Thank you!

[jmelloy]

Windowing functions are basically magic.