DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=k06eodfdQvE67zBXkZQb+N6OwJqknchOHYxGoBTLUeusrwB9oYL5eVdm1Tv0uqTHi4
	sYtnG+XBcFidyC1BnJFkYx57PUAHZIGZ9ks6o+BpHaPqgBFlTuG46ci8TbXiEBbH1cG7
	3EM2mwn9EGMgBnasOWGT14gJrFcS6L5OTiS88=
MIME-Version: 1.0
In-Reply-To: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.gmail.com>
References: <95f6bf9b0907100022u41173cl2ef880a3952d3996@mail.gmail.com>
Date: Fri, 10 Jul 2009 09:35:13 +0200
Message-ID: <162867790907100035w3547e208y13e7a64a3d1d46e7@mail.gmail.com>
Subject: Re: skip if latter value equal
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Marcin Krawczyk <jankes.mk@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 =3D true;
begin
  for r in select * from yourtablename loop order by ...
    if first then
      return next r;
      s :=3D r; first :=3D false;
    else
      if r.a is distinct from s.a then result.a :=3D r.a else result.a
:=3D NULL end if;
      if r.b is distinct from s.b then result.b :=3D r.b else result.b
:=3D NULL end if;
      if r.c is distinct from s.c then result.c :=3D r.c else result.c
:=3D NULL end if;
      if r.d is distinct from s.d then result.d :=3D r.d else result.d
:=3D NULL end if;
      return next result;
    end if;
    s :=3D 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 NUL=
L
> if latter value is exactly the same ? - for given ORDER BY clause, I gues=
s.
> For example, query returns:
>
> xxyy=C2=A0 1=C2=A0 4=C2=A0 true
> xxyy=C2=A0 5=C2=A0 7=C2=A0 true
> xxyy=C2=A0 21=C2=A0 8=C2=A0 true
> yyzz=C2=A0 5=C2=A0 1 false
> yyzz=C2=A0 7=C2=A0 7 false
> yyzz=C2=A0 8=C2=A0 34 false
>
> I'd like the output to be:
>
> xxyy=C2=A0 1=C2=A0 4=C2=A0 true
> NULL=C2=A0 5=C2=A0 7=C2=A0 NULL
> NULL=C2=A0 21=C2=A0 8=C2=A0 NULL
> yyzz=C2=A0 5=C2=A0 1 false
> NULL=C2=A0 7=C2=A0 7 NULL
> NULL=C2=A0 8=C2=A0 34 NULL
>
> Is there any magical trick to achieve this ?
>
> regards
> mk
>