Dynamic start date from specific column in a table (sysdate)

Question

I am pretty new in this field, trying to learn slowly so please be patient with me :)

My database contains a table called t_usage_interval. In this table there is a column name ID_Interval. Each month a new random 10 digit number is created in this column.

This is the query I am using

I would like to find out if there is a way to pull the latest interval by using column name DT_START with SYSDATE option? I guess it would be a dynamic query search from a sysdate to display the latest ID_Interval?

Thank you, A

Answer 1

This is how I understood the question.

A straightforward query returns row(s) whose dt_start is the first in that table that is lower or equal to sysdate (you might also use trunc(sysdate), if you don't care about time component). Drawback of this query is that it scans t_usage_Interval table twice.

select *
from t_usage_interval a
where a.dt_start = (select max(b.dt_start)
                    from t_usage_interval b
                    where b.dt_start <= sysdate
                   );
                   

Somewhat less intuitive option is to "rank" rows (whose dt_start is lower than sysdate) by dt_start, and then return row(s) that rank the "highest". This option scans the table only once, so it should perform better.

with temp as
  (select a.*,
     rank() over (order by a.dt_start desc) rn
   from t_usage_interval a
   where a.dt_start <= sysdate
  )
select t.*
from temp t
where t.rn = 1;