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.
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
CodePudding user response:
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;