i have a table with 3 fields. i.e.
id, transferdate, placeid
---------------------------
1 | 1-4-2014 | 14
2 | 4-4-2014 | 14
5 | 10-4-2014| 14
6 | 1-5-2013 | 13
9 | 10-6-2013| 12
What i would like to achieve...if possible...with a single query (no matter how many subqueries) but plain SQL (without pivot, CTE etc) is to get the same : placeid's transferdate from each row, on the previous row or to the next row so that i can make some calculations with them. i mean :
id, transferdate, placeid, nexttransferdate
--------------------------------------------
1 | 1-4-2014 | 14 | 4-4-2014
2 | 4-4-2014 | 14 | 10-4-2014
5 | 10-4-2014| 14 | null (or transferdate)
6 | 1-5-2013 | 13 | null (or transferdate)
9 | 10-6-2013| 12 | null (or transferdate)
I have achieved it with cursors in stored procedure or function or even using a temp table and i know how to do it with built-in recursive functions (i.e. Oracle) but my problem is that i need to use it as a subquery in a report SQL statement so it has to be plain SQL code as one statement.
Thank you for your answer
The SQL standard function to look into previous rows is LAG and to look into later rows is LEAD. They are not available in every dbms though. Just look it up, whether they are available.
If not: The next value is always the minimum value of all greater values, the previous value is the maximum of all smaller values. This should help you build a query.
EDIT: Here is a simple query without LEAD for you:
select
id,
transferdate,
placeid,
(
select min(transferdate)
from transfers latertransfers
where latertransfers.placeid = transfers.placeid
and latertransfers.transferdate > transfers.transferdate
) as nexttransferdate
from transfers
order by id;
EDIT: Here is the LEAD Version. Available in Oracle as of version 8.1.6 .
select
id,
transferdate,
placeid,
lead(transferdate) over (partition by placeid order by transferdate) as nexttransferdate
from transfers
order by id;