Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D4E13632ECD for ; Fri, 10 Jul 2009 04:46:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 68457-09 for ; Fri, 10 Jul 2009 04:45:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f223.google.com (mail-ew0-f223.google.com [209.85.219.223]) by mail.postgresql.org (Postfix) with ESMTP id CA10263246B for ; Fri, 10 Jul 2009 04:45:53 -0300 (ADT) Received: by ewy23 with SMTP id 23so744094ewy.19 for ; Fri, 10 Jul 2009 00:45:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=WWpe0Ja+U7kLLdzuIbc6jWdc/GfWz20F1vpw5MBYJYU=; b=nI3xmF/5rV/5N5pPDh0FBP+nzOOms7MyP0Lf2jnMMYXvh5PdV4duIG7m+K1Vs2h2OS 3zSCqHah1bz6650GR8lHRrcVV+w/T4cVj2v4Kzf0RbPlEuVEcQoEaRBgbBki03TjS7YL KCyoZldSvUIHBQw9c5/MPBSckAyS+L3BlFFLc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=nEkeJo7dLLAy5aurr/CpY1WsAWAOLQJ1XTVRikbw58FjdExoTgmmpELpDSXFvrVcAa 67Jnyu20RhVGHmW9bo06SyAhidcNGYN7lrbwvI6giLqzZoXS5rc7L3S2+eBAPBeB2kJe +IK8UTNdFwqj19PGronAfqt8al8uiSEP850wI= MIME-Version: 1.0 Received: by 10.210.81.3 with SMTP id e3mr817825ebb.77.1247211951115; Fri, 10 Jul 2009 00:45:51 -0700 (PDT) In-Reply-To: <162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com> References: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.gmail.com> <162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com> From: Marcin Krawczyk Date: Fri, 10 Jul 2009 09:45:31 +0200 Message-ID: <95f6bf9b0907100045j21dc0aa7k78fe73a3d698464e@mail.gmail.com> Subject: Re: skip if latter value equal To: Pavel Stehule Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174c15f04e4e30046e552947 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200907/52 X-Sequence-Number: 32793 --0015174c15f04e4e30046e552947 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Thanks a lot. pozdrowienia mk 2009/7/10 Pavel Stehule > 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 : > > 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 > > > --0015174c15f04e4e30046e552947 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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
=A0r yourtablename;
=A0s yourtablename;
=A0result youratblename;
=A0first boolean =3D true;
begin
=A0for r in select * from yourtablename loop order by ...
=A0 =A0if first then
=A0 =A0 =A0return next r;
=A0 =A0 =A0s :=3D r; first :=3D false;
=A0 =A0else
=A0 =A0 =A0if r.a is distinct from s.a then result.a :=3D r.a else result.= a
:=3D NULL end if;
=A0 =A0 =A0if r.b is distinct from s.b then result.b :=3D r.b else result.= b
:=3D NULL end if;
=A0 =A0 =A0if r.c is distinct from s.c then result.c :=3D r.c else result.= c
:=3D NULL end if;
=A0 =A0 =A0if r.d is distinct from s.d then result.d :=3D r.d else result.= d
:=3D NULL end if;
=A0 =A0 =A0return next result;
=A0 =A0end if;
=A0 =A0s :=3D r;
=A0end loop;
=A0return;
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 g= uess.
> For example, query returns:
>
> xxyy=A0 1=A0 4=A0 true
> xxyy=A0 5=A0 7=A0 true
> xxyy=A0 21=A0 8=A0 true
> yyzz=A0 5=A0 1 false
> yyzz=A0 7=A0 7 false
> yyzz=A0 8=A0 34 false
>
> I'd like the output to be:
>
> xxyy=A0 1=A0 4=A0 true
> NULL=A0 5=A0 7=A0 NULL
> NULL=A0 21=A0 8=A0 NULL
> yyzz=A0 5=A0 1 false
> NULL=A0 7=A0 7 NULL
> NULL=A0 8=A0 34 NULL
>
> Is there any magical trick to achieve this ?
>
> regards
> mk
>

--0015174c15f04e4e30046e552947--