Ordering by a max or a min from another table

Question

I have a table that consists of a unique id, and a few other attributes. It holds "schedules". Then I have another table that holds a list of all the times each schedule has or will "fire". This isn't the exact schema, but it's close:

create table schedule (
   id varchar(40) primary key,
   attr1 int,
   attr2 varchar(20)
);

create table schedule_times (
   id varchar(40) foreign key schedule(id),
   fire_date date
);

I want to query the schedule table, getting the attributes and the next and previous fire_dates, in Java, sometimes ordering on one of the attributes, but sometimes ordering on either previous fire_date or the next fire_date. Ordering by the attributes is easy, I just stick an "order by" into the string while I'm building my prepared statement. I'm not even sure how to go about selecting the last fire_date and the next one in a single query - I know that I can find the next fire_date for a given id by doing a

SELECT   min(fire_date)
FROM     schedule_times
WHERE    id = ? AND
         fire_date > sysdate;

and the similar thing for previous fire_date using max() and fire_date < sysdate. I'm just drawing a blank on how to incorporate that into a single select from the schedule so I can get both next and previous fire_date in one shot, and also how to order by either of those attributes.

Answer 1

You can do that using two sub-queries in Left Joins.
This has the advantage of returning NULL for your fire_dates if there is no next/previous schedule.

Select id, attr1, attr2, next_fire_date, previous_fire_date
From schedule s
Left Join ( Select id, Min(fire_date) As next_fire_date
            From schedule_times st
            Where st.fire_date > Sysdate
            Group By id ) n
    On ( n.id = s.id )
Left Join ( Select id, Max(fire_date) As previous_fire_date
            From schedule_times st
            Where st.fire_date < Sysdate
            Group By id ) p
    On ( p.id = s.id )

You can then add your ORDER BY on next_fire_date or previous_fire_date.

---

If performance matters, create a compound index on schedule_times( id, fire_date ), this will allow the sub-queries to read only from this index.

Answer 2

Try something like this:

select schedule.*,
(
    select max(si.fire_date) from schedule_times si where si.id = schedule.id and si.fire_date < sysdate
) as prevfire,

(
    select min(si.fire_date) from schedule_times si where si.id = schedule.id and si.fire_date > sysdate
) as nextfire
from schedule
where id = ?
order by attr1

Answer 3

Modify the query to

SELECT Distinct S.ID, ATTR1, ATTR2, LEAD(FIRE_DATE, 1, SYSDATE) OVER (PARTITION BY S.ID ORDER BY S.ID) NEXT_FIRE_DATE, LAG(FIRE_DATE, 1, SYADATE) OVER (PARTITION BY S.ID ORDER BY S.ID) PREV_FIRE_DATE FROM SCHEDULE S,SCHEDULE_TIMES ST WHERE ST.ID=S.ID;

THIS WAS SIMPLE QUERY. YOU CAN TRY THIS.

LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row. The general syntax of LEAD is shown below:

LEAD (sql_expr, offset, default) OVER (analytic_clause)

1. sql_expr is the expression to compute from the leading row.
2. offset is the index of the leading row relative to the current row. offset is a positive integer with default 1.
3. default is the value to return if the offset points to a row outside the partition range.

The syntax of LAG is similar except that the offset for LAG goes into the previous rows.