Postgres Pro
Downloads
Home   >   mailing lists

Thread: Finding missing values in sequence column

Finding missing values in sequence column

From
Lukasz Brodziak
Date:
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

From
Szymon Guz
Date:
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

From
Jasen Betts
Date:
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