oracle query: get max value with some conditions

Question

I have a table called service_t,it has a column effective_dt which is populated with unix timestamp. I need find all rows with max effective_dt but the effective_dt must be less than a given value. I have the following sql but I don’t think it’s efficient:

Select *
  from service_t t1
 where t1.effective_dt <= :given_value
   and t1.effective_dt = (select max(effective_dt)
                            from service_t t2
                           where t2.effective_dt <= :given_value
                             and t1.id = t2.id)

Is this efficient or any other good ways? Thanks!

Do you mean efficient in terms of performance or just a better way to code this? — Anjan Biswas
– Anjan Biswas, Commented Oct 5, 2012 at 14:19

Frank Schmitt · Accepted Answer · 2012-10-05 14:21:59Z

2

Using analytic functions is probably more efficient:

Select * from (
  select t1.*, 
       dense_rank() over (partition by id order by effective_dt desc) rn
  from service_t t1
 where t1.effective_dt <= :given_value)
where rn = 1

answered Oct 5, 2012 at 14:21

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

oracle query: get max value with some conditions

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related