From: Pavel Stehule <pavel.stehule@gmail.com>
To: Marcin Krawczyk <jankes.mk@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: skip if latter value equal
Date: Fri, 10 Jul 2009 09:35:13 +0200
Message-ID: <162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com> (raw)
In-Reply-To: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.gmail.com>
References: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.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: pavel.stehule@gmail.com, jankes.mk@gmail.com
Subject: Re: skip if latter value equal
In-Reply-To: <162867790907100035w3547e208y13e7a64a3d1d46e7@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