Обсуждение: Finding missing values in sequence column

Поиск
Список
Период
Сортировка

Finding missing values in sequence column

От
Lukasz Brodziak
Дата:
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"

Re: Finding missing values in sequence column

От
Szymon Guz
Дата:
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

Re: Finding missing values in sequence column

От
Jasen Betts
Дата:
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