From: Marcin Krawczyk <jankes.mk@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: skip if latter value equal
Date: Fri, 10 Jul 2009 09:45:31 +0200
Message-ID: <95f6bf9b0907100045j21dc0aa7k78fe73a3d698464e@mail.gmail.com> (raw)
In-Reply-To: <162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com>
References: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.gmail.com>
<162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com>
Thanks a lot.
pozdrowienia
mk
2009/7/10 Pavel Stehule <pavel.stehule@gmail.com>
> Hello
>
> you can do it simply in new PostgreSQL 8.4. In older version the best
> way what I know is using a stored procedure, that returns table
>
> create or replace function foo()
> returns setof yourtablename as $$
> declare
> r yourtablename;
> s yourtablename;
> result youratblename;
> first boolean = true;
> begin
> for r in select * from yourtablename loop order by ...
> if first then
> return next r;
> s := r; first := false;
> else
> if r.a is distinct from s.a then result.a := r.a else result.a
> := NULL end if;
> if r.b is distinct from s.b then result.b := r.b else result.b
> := NULL end if;
> if r.c is distinct from s.c then result.c := r.c else result.c
> := NULL end if;
> if r.d is distinct from s.d then result.d := r.d else result.d
> := NULL end if;
> return next result;
> end if;
> s := r;
> end loop;
> return;
> end;
> $$ language plpgsql;
>
> select * from foo();
>
> regards
> Pavel Stehule
>
> 2009/7/10 Marcin Krawczyk <jankes.mk@gmail.com>:
> > Hi list,
> >
> > I was wondering if it was possible for a field in SQL query to return
> NULL
> > if latter value is exactly the same ? - for given ORDER BY clause, I
> guess.
> > For example, query returns:
> >
> > xxyy 1 4 true
> > xxyy 5 7 true
> > xxyy 21 8 true
> > yyzz 5 1 false
> > yyzz 7 7 false
> > yyzz 8 34 false
> >
> > I'd like the output to be:
> >
> > xxyy 1 4 true
> > NULL 5 7 NULL
> > NULL 21 8 NULL
> > yyzz 5 1 false
> > NULL 7 7 NULL
> > NULL 8 34 NULL
> >
> > Is there any magical trick to achieve this ?
> >
> > regards
> > mk
> >
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jankes.mk@gmail.com, pavel.stehule@gmail.com
Subject: Re: skip if latter value equal
In-Reply-To: <95f6bf9b0907100045j21dc0aa7k78fe73a3d698464e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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