How to write sql LEAD() and LAG() query in interbase?

Question

This query gives the desired results when working with Oracle as my back-end.

select field1,
         field2 
from 
   (select field1, 
           field2, 
           LEAD(field2) over (order by r) name 
    from 
       (select rownum r,
              field1,
              field2 
       from t3 
       where field2 = 1
       )
   ) 
where v != nvl(name, -1);

But I am facing issues while querying to Interbase database because analytical functions lag() and lead()are not supported in InterBase6.

My requirement is, if array field2={1,3,4,4,3,4}, then I should get results as {1,3,4,3,4}.

Consider switching to Firebird 3, [it supports `lead` and `lag`](http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html#d0e6678). BTW you are still on Interbase 6? That was released 17 years ago... around the time Firebird 0.x was forked from Interbase 6. — Mark Rotteveel, Apr 15 '17 at 12:42
@Ashish4434 - I see that the question is still opened. If you are satisfied with any of the answer, then `accept` it, so the question could be closed. Read [What to do when someone answers my question](http://stackoverflow.com/help/someone-answers). Also try to close your previous questions as well. — Utsav, May 01 '17 at 06:52

score 1 · Answer 1 · answered Apr 15 '17 at 09:47

I have no idea on interbase but I can give you points to write a query to replicate lead function. You can build on that.

Find a column you want to order the rows by. The rownum you are using in your oracle query is not a correct way to do so. As you cannot guarantee that the results will be displayed in same order all the time. So assuming field1 is the column you want to order by.

Now find a way to generate a sequence number from 1 - n in interbase, based on the order above. where n being your total number of rows. So your sample table might look like below.

+-----+--------+--------+
| seq | field1 | field2 |
+-----+--------+--------+
|   1 |    101 |      1 |
|   2 |    102 |      3 |
|   3 |    103 |      4 |
|   4 |    104 |      4 |
|   5 |    105 |      3 |
+-----+--------+--------+

Once you have achieved the above task, you are almost done. You just have to do a self join to replicate lead or lag function. Here is example of lead

select t1.field1
 ,t1.field2
 ,t2.field1 as lead_field1
 ,t2.field2 as lead_field2
from table1 t1
left join
(select seq-1 as prev_seq,field1,field2
from table1) t2
on t1.seq=t2.prev_seq

The output of this query will be like this, which is what you need to replicate lead function.

+--------+--------+-------------+-------------+
| field1 | field2 | lead_field1 | lead_field2 |
+--------+--------+-------------+-------------+
|    101 |      1 | 102         | 3           |
|    102 |      3 | 103         | 4           |
|    103 |      4 | 104         | 4           |
|    104 |      4 | 105         | 3           |
|    105 |      3 | NULL        | NULL        |
+--------+--------+-------------+-------------+

Sample for the same could be found at

http://rextester.com/IJIU40428

How to write sql LEAD() and LAG() query in interbase?

1 Answers1