CODEWITHSUNDEEP
sqloracle-databaseplsql
Gary
Gary

Reputation: 4725

oracle query: get max value with some conditions

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!

Upvotes: 0

Views: 4122

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30835

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

Upvotes: 2

Related Questions