I have the sql using Oracle SQL Developer below that works, but I am concerned about performance over a larger live database.
Please excuse all the join tables, this is showing the connections to the _aud table and revision_table that keeps the audit history.
select cust.forename, cust.surname
from customer cust
join section_details sd on cust.section = sd.section
where
-- this value is substituted programatically
sd.type = 5 and
(
(select max(rt.timestamp)
from
customer cust_tmp1
join section_details sd on cust_tmp1.section = sd.section
join measure msr on sd.measure = msr.id
join measure_aud msr_a on msr.id = msr_a.id
join revision_table rt on msr_a.rev = rt.id
where
cust_tmp1.id = cust.id and msr.measure = 'Last Contact Date')
>
(select max(rt.timestamp)
from
customer cust_tmp2
join section_details sd on cust_tmp2.section = sd.section
join measure msr on sd.measure = msr.id
join measure_aud msr_a on msr.id = msr_a.id
join revision_table rt on msr_a.rev = rt.id
where
cust_tmp2.id = cust.id and msr.measure = 'Last Order Date')
);
In short I only want to retrieve the customer details if the 'Last Contact Date' is more recent than the 'Last Order Date'. I end up checking the max(timestamp) from one select is greater than the max(timestamp) from the other select.
It all works fine and I get the results I am expecting.
Each side of the timestamp comparison is duplicated apart from the msr.measure = 'Last Contact Date' or 'Last Order Date'.
I have tried a few alternatives that never actually worked as they resulted in multi level nested sub queries and I couldn't pass in the outer cust record.
Any further ideas would be greatly appreciated.
I tried the solutions posted here and they all seem to work, many thanks for the responses - I hadn't investigated the having clause before.
I added any required indexes and ran explain plans on all the options, my original query had the lowest cost. So I think I will use that option moving forward.