We effectively find the rows where a <(max (a) for a given b)

Question

I have a table structure like:

a | b
2014-04-12| 3
2014-03-12| 3
2014-02-12| 3
2014-05-12| 4
2014-03-12| 4
2014-04-12| 4

I need a conclusion where a is less max(a)for a specific b, and also less now().

What I have been doing so far is that I am doing an independent join to b, where it a < now()hastable.a < max(table1.b)

And the conclusion is correct, but the cost of the query is very high, since the number of rows in my table is quite large. Is there an alternative way to do this.

My request:

select a1.a, a1.b 
from tab a1 
JOIN tab b1 
    on a1.b=b1.b 
where a1.a < now() 
group by a1.a, a1.b 
having a1.a < max(b1.a);

Answer 1

I think this should be faster than self-join, since only one table scan is required:

select a,b
from (
  select a, 
         b, 
         max(a) over (partition by b) as max_a
  from the_table
  where a < now()
)
where a < max_a;

a < now() , (a,b) . , (b,a) max a . ,