CODEWITHSUNDEEP
sqloracle-databaseselectoracle11granking
thursdaysgeek
thursdaysgeek

Reputation: 7946

Join the best record, if there is one, in Oracle

I have a fairly complex Oracle query, getting data from multiple tables. In one of the joins, I want the best record, if there is one. Therefore, a left outer join. There is a start date field, so for most records, getting the max start date will get me the best record. However, occasionally there are records that have the same start date. In that case, there is also a status field. However, the best status value is not a min or a max. '20' is best, '05' or '40' are ok, and '70' is worst. How can I set up the query to find the best option when multiple records are returned?

So, if I have the following data

Table1                       Table2
ID     otherData             ID      date      status    otherData
1      stuffa                1       jan-1-13  20        stuff93
2      stuff3                
3      stuff398              3       jan-2-13  20        stuff92
                             3       jan-2-13  70        stuff38
                             3       dec-3-12  20        stuff843

I will be able to query and get the following:

1   stuffa    jan-1-13  20  stuff93
2   stuff3
3   stuff398  jan-2-13  20  stuff92

Right now, my query is as follows, which gets a second record 3 with the 70 status:

select *
from table1 t1
left outer join
   (select *
    from table2 t2a
    where t2a.date = (select max(t2b.date)
                        from table2 t2b
                       where t2b.id = t2a.id)
    ) t2
  on (t2.id = t1.id)

Is there a way to set an ordered enumeration or something like that within a select statement? Something like

rank() over ( partition by status order by ('20','05','40','70') rank

Upvotes: 0

Views: 128

Answers (3)

Alex Poole
Alex Poole

Reputation: 191415

You could do something like:

select t1.id, t1.otherdata, t2.dt, t2.status, t2.otherdata 
from table1 t1
left outer join (
    select t2a.*,
        row_number() over (partition by id order by dt desc,
            case status
                when '20' then 1
                when '05' then 2
                when '40' then 3
                when '70' then 4
                else 5 end) as rn
    from table2 t2a
    ) t2 on t2.id = t1.id and t2.rn = 1
order by t1.id;

This assumes you want a single hit even if there are two with the same status; which of the two you get is indeterminate. If you wanted both you could use rank() instead. Either way you're assigning a rank to each record based on the date (descending, since you want the max) and your own order for the status values, and then only ever picking the highest ranked in the join condition.

With data set up as:

create table table1(id number, otherdata varchar2(10));
create table table2(id number, dt date, status varchar2(2), otherdata varchar2(10));

insert into table1 values(1, 'stuffa');
insert into table1 values(2, 'stuff3');
insert into table1 values(3, 'stuff398');

insert into table2 values(1, date '2013-01-01', '20', 'stuff93');
insert into table2 values(3, date '2013-01-02', '20', 'stuff92');
insert into table2 values(3, date '2013-01-02', '70', 'stuff38');
insert into table2 values(3, date '2012-12-03', '20', 'stuff843');

... this gives:

        ID OTHERDATA  DT        STATUS OTHERDATA
---------- ---------- --------- ------ ----------
         1 stuffa     01-JAN-13 20     stuff93    
         2 stuff3                                 
         3 stuff398   02-JAN-13 20     stuff92

Upvotes: 0

DazzaL
DazzaL

Reputation: 21993

add the status to the order by like this;

select *
  from (select t1.id,  t1.otherdata otherdatat1, t2.date, t2.status, t2.otherdata otherdatat2,
               rank() over (partition by t1.id order by t2.date desc,
                            case t2.status
                              when '20' then 1
                              when '05' then 2
                              when '40' then 3
                              when '70' then 4
                              else 5
                            end) rnk
          from table1 t1
               left outer join table2 t2
                            on t1.id = t2.id)
 where rnk = 1;

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

If the ordered enumeration has few elements you can use this

 ........         order by 
                  CASE status WHEN '20' THEN 1
                              WHEN '05' THEN 2
                              WHEN '40' THEN 3
                              WHEN '70' THEN 4
                  END) rank

Upvotes: 1

Related Questions