Ordering by a max or a min from another table
- by Paul Tomblin
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.
