Order for max or min from another table
I have a table consisting of a unique id and several other attributes. It contains "graphs". Then I have another table that contains a list of all the cases that each graph has or will "shoot". This is not an exact diagram, but it closes:
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, get the attributes and the next and previous fire_dates in Java, sometimes ordering one of the attributes, but sometimes ordering either the previous fire_date or the next fire_date. Arranging by attributes is easy, I just insert "order" into the string while I build my prepared statement. I'm not even sure how to go to fetch the last fire_date and the next one in the same request - I know that I can find the next fire_date for a given id by doing
SELECT min(fire_date)
FROM schedule_times
WHERE id = ? AND
fire_date > sysdate;
and a similar thing for the previous fire_date with max()
and fire_date < sysdate
. I'm just drawing a space on how to include this in one selection from the schedule so that I can get both the next and previous fire_date in one shot, and also how to order any of those attributes.
a source to share
You can do this using two subqueries in Left Join
s.
This has the advantage that you return 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 )
Then you can add ORDER BY
to next_fire_date
or previous_fire_date
.
If performance matters, create a composite index on schedule_times( id, fire_date )
, this will only allow subqueries to read from that index.
a source to share
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
a source to share
Change your request to
SELECT Differences . 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 A SIMPLE QUESTION. YOU CAN TRY THIS.
LEAD has the ability to evaluate the expression on the next lines (lines that will come after the current line) and return the value in the current line. Following is the general LEAD syntax:
LEAD (sql_expr, offset, default) OVER (analyttic_clause)
- sql_expr is an expression to evaluate from a leading string.
- offset is the index of the leading line relative to the current row. offset is a positive integer by default 1.
- default is the value returned if the offset points to a string outside the section range.
The LAG syntax is the same, except that the offset for the LAG goes to the previous lines.
a source to share