Mr.Maze Mr.Maze - 1 month ago 10
SQL Question

Is it possible to combine these two queries together other than using temp tables?

Query 1:

SELECT MAX(START_DATE) AS HIGHEST_DT
FROM T;


Query 2:

SELECT
START_DATE AS LOWER_DT
FROM T
WHERE END_DATE = HIGHEST_DT;


I'm hoping to get something like

START_DATE HIGHEST_DT

Answer

So, it seems you have a table t with two columns, start_date and end_date (and maybe more columns); you want to find the most recent (max) start_date, and then to find all the rows where the end_date is equal to this max(start_date), right?

One way is (not tested since you didn't provide test data):

select start_date as lower_dt,  highest_dt
from   (select start_date, end_date, max(start_date) over () as highest_dt
        from   t)
where  end_date = highest_dt;
Comments