Choosing the Most Recently Entered Non-Blank Value When Many Values Exist

Question

I have run into a problem where some IDs have multiple names associated with them. That is, if I have a location ID from one table and need to pull the location's name from another table based on the location ID, the query is returning multiple names. I want to choose the newest name based on a timestamp, if that one is not blank (that is, it's not null, but many of the entries are just a blank space).

TABLE LOCATIONS:

LOCATION_ID   LOCATION_NAME    LOCATION_TS
  12345         'BEST AUTO'        4
  12345         'BESTAUTO'         3
  12345         ' '                5
  12347         'FETTS'            3
  12347         'FESTS'            2

TABLE LOCATION_DETAIL:

LOCATION_ID    LOCATION_TYPE
  12345         'AA'
  12347         'ZQ'

I am trying to get a result that looks like this:

LOCATION_ID   LOCATION_NAME    LOCATION_TYPE
  12345         'BEST AUTO'        'AA'
  12347         'FETTS'            'ZQ'

Here is an SQLfiddle. I am using Oracle 11g. I have been spinning my wheels on this for a while and I feel like I'm missing something obvious. I should mention that the way my permissions are set up, pretty much the only thing I can do is query the database, not create views or tables, update data, etc.

Answer 1

What you're looking for is generally known as a "greatest of group" solution. There are several patterns for this. A search of dba.SE or SO will show you some.

The posted question and the SQL Fiddle have the table names swapped. This code is from the Fiddle:

with NEWEST as
(
    select
        LOCATION_ID,
        MAX(LOCATION_TS) as MAX_TS
    from Location_detail
    where Location_name is not null
    and LOCATION_NAME <> ' '         -- or whatever your exclusion conditions are
    group by LOCATION_ID
)
select
    l.LOCATION_ID,
    ld.LOCATION_NAME,
    l.LOCATION_TYPE
from LOCATIONS l
LEFT OUTER JOIN NEWEST n
ON l.LOCATION_ID = n.LOCATION_ID
LEFT OUTER JOIN LOCATION_DETAIL ld
on ld.LOCATION_ID = n.LOCATION_ID
and ld.LOCATION_TS = n.MAX_TS;