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
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;