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;
> 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 >