Choosing the Most Recently Entered Non-Blank Value When Many Values Exist
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 &lt;&gt; ' '         -- 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;