pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedskip if latter value equal
3+ messages / 2 participants
[nested] [flat]
* skip if latter value equal
@ 2009-07-10 07:22 Marcin Krawczyk <jankes.mk@gmail.com>
2009-07-10 07:35 ` Re: skip if latter value equal Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Marcin Krawczyk @ 2009-07-10 07:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: skip if latter value equal
2009-07-10 07:22 skip if latter value equal Marcin Krawczyk <jankes.mk@gmail.com>
@ 2009-07-10 07:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-07-10 07:45 ` Re: skip if latter value equal Marcin Krawczyk <jankes.mk@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2009-07-10 07:35 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: skip if latter value equal
2009-07-10 07:22 skip if latter value equal Marcin Krawczyk <jankes.mk@gmail.com>
2009-07-10 07:35 ` Re: skip if latter value equal Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-07-10 07:45 ` Marcin Krawczyk <jankes.mk@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Marcin Krawczyk @ 2009-07-10 07:45 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
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
> >
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-07-10 07:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-10 07:22 skip if latter value equal Marcin Krawczyk <jankes.mk@gmail.com>
2009-07-10 07:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-07-10 07:45 ` Marcin Krawczyk <jankes.mk@gmail.com>
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