Thread: DISTINCT ON ... without distinct null values ?

Hello,
I've got a table which I query with something like " SELECT DISTINCT ON("c")
FROM table ... " .

Not every row in the column "c" does have a value, some are null.
My problem is, that I want to eliminate double values in "c" only if they
are not null !

Does anybody has an idea ?

Thx
Albrecht

On Thu, Apr 18, 2002 at 04:43:25PM +0200, Albrecht Berger wrote:
> Hello,
> I've got a table which I query with something like " SELECT DISTINCT ON("c")
> FROM table ... " .
> 
> Not every row in the column "c" does have a value, some are null.
> My problem is, that I want to eliminate double values in "c" only if they
> are not null !

CREATE SEQUENCE tmp_seq;
SELECT DISTINCT ON (COALESCE("c", nextval('tmp_seq')) * FROM table ...
DROP SEQUENNCE tmp_seq;

or even

SELECT DISTINCT ON (COALESCE("c", random())) * FROM table ...

Not sure I'm right...

-- 
Fduch M. Pravking