On 30 December 2010 12:19, Lukasz Brodziak <lukasz.brodziak@xxxxxxxxx> wrote:
Hello,
I have a problem with writing a select statement that would return
records for which difference in values of this and following ID is
bigger than 1. Example table
ID Â Â ÂData
1 Â Â Â Text
2 Â Â Â Text2
5 Â Â Â Text5
23 Â Â Text23
24 Â Â TXT
25 Â Â RRRR
So I need to return rows with IDs 2,5,23.
Hi,
sorry, I don't get it. If difference between current and following id should be bigger than 1, then returned rows should be only 2 and 5. Not 23.
You can do it this way:
select lagÂ
fromÂ
ÂÂ(selectÂ
ÂÂ Â id,Â
ÂÂ Â lag(id) over (order by id)Â
ÂÂ from xÂ
ÂÂ order by id) aÂ
whereÂ
ÂÂid-lag > 1;
regards
Szymon
Szymon
