Get Value from Previous row/next row SQL

Question

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

So it doesn't have to be agnostic? You tagged is a such but your explanation says it is in sql server. Assuming it is sql server which version are you running? And you can use a cte in a report. — Sean Lange, Sep 04 '14 at 14:03
@SeanLange: i have tagged it as `database-agnostic` since OP mentioned it several times. You don't know which kind of _report_ he is referring to. — Tim Schmelter, Sep 04 '14 at 14:06
The SQL standard defines the functions `lead()` and `lag()` to do that - so this can be considered "database agnostic". And all modern DBMS support that and can be considered "state-of-the-art" feature — , Sep 04 '14 at 14:06
@TimSchmelter I know. That is why I asked for clarification. The OP mentions that it should be agnostic but also mentions CTE which is a bit confusing. I know they said they want to avoid it but my guess is because they don't realize you can use them in a report. — Sean Lange, Sep 04 '14 at 14:11
with "SQL report statement" i meant use it as retrieving query for some reporting engine. Alas our reporting engine is custom made so it does accept only select queries and that is my problem. The database i refer to is oracle but i have put any sql database for making clear a general solution and not one for certain system. Sorry if i messed it up. Thank you for the comments anyway. — ckinfos, Sep 04 '14 at 14:24

Thorsten Kettner · Accepted Answer · 2014-09-04T14:30:27.150

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;

thank you for your answer. it does work same as of[link] (@GordonLinoff) but the query plan shows yours to run faster. — ckinfos, Sep 04 '14 at 14:31
I've also added the LEAD query. This is the straight-forward way to do it and should result in the best execution plan. — Thorsten Kettner, Sep 04 '14 at 14:35

score 1 · Answer 2 · answered Sep 04 '14 at 14:14

1

You can do this with a self-join and aggregation:

select t.id, t.transferdate, t.placeid, min(t.transferdate)
from table t left join
     table tnext
     on tnext.placeid = t.placeid and tnext.transferdate > t.transferdate
group by t.id, t.transferdate, t.placeid;

That said, I would not recommend actually using this query. In virtually every database, there are better approaches.

answered Sep 04 '14 at 14:14

Gordon Linoff

1,242,037
58
646
786

it works well for what i need but it surely does a strange query plan as u mention. i have to give the answe to the kettner's query. Thank you both anyway. – ckinfos Sep 04 '14 at 14:37

Get Value from Previous row/next row SQL

2 Answers2

Linked