From: | Szymon Guz <mabewlun(at)gmail(dot)com> |
---|---|
To: | Lukasz Brodziak <lukasz(dot)brodziak(at)gmail(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Finding missing values in sequence column |
Date: | 2010-12-30 11:48:31 |
Message-ID: | AANLkTi=m4zNuzpzrTa6JdY3OaOA2gnkYzi8dzw8+HLzi@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Ribe | 2010-12-30 15:10:49 | Re: pg v. 8.4.5 misses objects and data after restoring from backup using wal |
Previous Message | Lukasz Brodziak | 2010-12-30 11:19:12 | Finding missing values in sequence column |