pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding missing values in sequence column
3+ messages / 3 participants
[nested] [flat]
* Finding missing values in sequence column
@ 2010-12-30 11:19 Lukasz Brodziak <lukasz.brodziak@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Lukasz Brodziak @ 2010-12-30 11:19 UTC (permalink / raw)
To: pgsql-admin
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"
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding missing values in sequence column
@ 2010-12-30 11:48 Szymon Guz <mabewlun@gmail.com>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Szymon Guz @ 2010-12-30 11:48 UTC (permalink / raw)
To: Lukasz Brodziak <lukasz.brodziak@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding missing values in sequence column
@ 2010-12-30 18:49 Jasen Betts <jasen@xnet.co.nz>
parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2010-12-30 18:49 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-12-30 18:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-30 11:19 Finding missing values in sequence column Lukasz Brodziak <lukasz.brodziak@gmail.com>
2010-12-30 11:48 ` Szymon Guz <mabewlun@gmail.com>
2010-12-30 18:49 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox