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

oracletimestamp

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.

Best Answer

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;

Related Solutions

How to Oracle use a Range Scan here

You have a leading column specified in condition (Location = :location) . From here :

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
col1 = :b1
...

Oracle: How to query a Hierarchical table

FrusteratedWithFormsDesigner has the right direction(+1). Here is what I think you are looking for specifically.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds

The view accomplishes all three goals. You can query it for a building to find everything it contains and you can query it for a sub-location to find what building it is in.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

If you don't want to count the building itself as one of the sub-locations you can wrap the existing query in one the eliminates entries in which the building and sublocation are the same.

Related Question