Re: Finding missing values in sequence column

On 30 December 2010 12:19, Lukasz Brodziak <lukasz(dot)brodziak(at)gmail(dot)com>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