Search code examples
sqloracle-databaseselectjoinsubstr

Selecting the most recent row by timestamp for multiple entries while joining another table with substring of the current one

Ok, the title sounds awfully complicated, but what I actually want to do is not that complex. My tables are:

Servicestatustable:

ServiceIdentifier           ServiceStatus   Timestamp

System1-Service1            1               sometimestamp
System1-Service1            1               sometimestamp
System2-Service1            0               sometimestamp
System2-Service1            1               sometimestamp
System1-Service2            1               sometimestamp
System1-Service2            0               sometimestamp
System2-Service2            1               sometimestamp
System2-Service2            1               sometimestamp
System3-Service42           0               sometimestamp

Systemnametable:

SystemIdentifier   SystemName

System1            Baconsystem
System2            LoremIpsumSystem
System3            System42

Desired Output:

Baconsystem, Service1, 1, sometimestamp
Baconsystem, Service2, 1, sometimestamp
LoremIpsumSystem, Service1, 1, sometimestamp
LoremIpsumSystem, Service2, 1, sometimestamp
System42, Service42, 0, sometimestamp

The SQL Query (Oracle) should only output the most recent entry for each system-service combination, according to the timestamp. I just can't get the join over the substring to work properly when combined with trying to get every system-service combination to be output only once. Any help is appreciated.

Solution

  • One of possibilities is solution with functions substr(), instr(), row_number():

    select sn, sv, sst, st 
  from (
    with st as (
      select 
          substr(ServiceIdentifier, 1, instr(ServiceIdentifier, '-')-1) syst,
          substr(ServiceIdentifier, instr(ServiceIdentifier, '-')+1) service, 
          ServiceStatusTable.*
        from ServiceStatusTable)
    select sn.systemname sn, st.service sv, st.servicestatus sst, st.servicetime st,
        row_number() over (partition by systemname, service 
          order by st.servicetime desc) rn
      from st join Systemnametable sn on st.syst = sn.systemidentifier )
  where rn = 1

    SQLFiddle