Обсуждение: Finding missing values in sequence column
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. -- Łukasz Brodziak "What if everyting around You isn't quite as it seems, What if all the world You think You know is an inelaborate dream When You look at Your reflection is that all you want it to be What if You could look right through the cracks Would You find Yourself...... Find Yourself afraid to see"
On 30 December 2010 12:19, Lukasz Brodziak <lukasz.brodziak@gmail.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
Szymon
On 2010-12-30, Lukasz Brodziak <lukasz.brodziak@gmail.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. SELECT id FROM Example EXCEPT SELECT id-1 FROM Example; select * FROM Example WHERE id+1 NOT IN ( SELECT id FROM Example ); > -- > Łukasz Brodziak > "What if everyting around You isn't quite as it seems, > What if all the world You think You know is an inelaborate dream > When You look at Your reflection is that all you want it to be > What if You could look right through the cracks > Would You find Yourself...... Find Yourself afraid to see" > -- ⚂⚃ 100% natural