Postgres Pro
Downloads
Home   >   mailing lists

Re: COUNT(DISTINCT field) OVER (PARTITION BY another_field) - Mailing list pgsql-admin

From Daniel Cristian Cruz
Subject Re: COUNT(DISTINCT field) OVER (PARTITION BY another_field)
Date
Msg-id 48d0cacb0907210532p7941a9f2i4f7b6bc7407b1131@mail.gmail.com
Whole thread Raw
List pgsql-admin
Tree view
Auto-answer:

WITH uniques AS (
 SELECT group_key, COUNT(DISTINCT reference) AS unique_references
 FROM record_data
 GROUP BY group_key
)
SELECT
 group_key,
 record_data.*,
 uniques.unique_references
FROM record_data
JOIN uniques
 USING (group_key)
ORDER BY
 group_key;

Super-PostgreSQL is my hero...

2009/7/21 Daniel Cristian Cruz <danielcristian@gmail.com>
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
クルズ  クリスチアン ダニエル



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

pgsql-admin by date:

Previous
From: Andreas Wenk
Date:
Subject: Re: implement ldap authentication in PostgreSQL
Next
From: Daniel Cristian Cruz
Date:
Subject: COUNT(DISTINCT field) OVER (PARTITION BY another_field)