Skip site navigation (1) Skip section navigation (2)

COUNT(DISTINCT field) OVER (PARTITION BY another_field)

From: Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: COUNT(DISTINCT field) OVER (PARTITION BY another_field)
Date: 2009-07-21 11:59:58
Message-ID: 48d0cacb0907210459l4afbfda0jf5074067c8b960c9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi list,

Does someone knows why this is not a supported feature?

I was happily SQL querying when I woke up from my dreams with this message:

ERROR:  DISTINCT is not implemented for window functions
LINE 6:  COUNT(DISTINCT handle) OVER (PARTITION BY pk_pessoas) AS ha...

It could be very useful to find unique references within groups of clone
records. Or there is another way to write this kind of query?

Example (reference is a column of record_data):

SELECT
 group_key,
 record_data.*,
COUNT(DISTINCT reference) OVER (PARTITION BY group_key) AS unique_references
FROM record_data
ORDER BY
 group_key;

Regards,
-- 
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル

Responses

pgsql-admin by date

Next:From: Daniel Cristian CruzDate: 2009-07-21 12:32:01
Subject: Re: COUNT(DISTINCT field) OVER (PARTITION BY another_field)
Previous:From: Andreas WenkDate: 2009-07-21 10:38:31
Subject: Re: implement ldap authentication in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group