Postgres Pro
Downloads
Home   >   mailing lists

Thread: skip if latter value equal

skip if latter value equal

From
Marcin Krawczyk
Date:
Hi list,<br /><br />I was wondering if it was possible for a field in SQL query to return NULL if latter value is
exactlythe same ? - for given ORDER BY clause, I guess.<br />For example, query returns:<br /><br />xxyy  1  4  true<br
/>xxyy  5  7  true<br />xxyy  21  8  true<br />yyzz  5  1 false<br />yyzz  7  7 false<br />yyzz  8  34 false<br /><br
/>I'dlike the output to be:<br /><br /> xxyy  1  4  true<br />NULL  5  7  NULL<br /> NULL  21  8  NULL<br /> yyzz  5  1
false<br/>NULL  7  7 NULL<br />NULL  8  34 NULL<br /><br />Is there any magical trick to achieve this ?<br /><br
clear="all"/>regards<br />mk<br />

Re: skip if latter value equal

From
Pavel Stehule
Date:
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
>

Re: skip if latter value equal

From
Marcin Krawczyk
Date:
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
>