count (DISTINCT field) OVER ()

[Tarlika Elisabeth Schmitz <postgresql6@xxxxxxxxxxxxxxxxxxxx>]

I would like to implement the equivalent of "count (DISTINCT field) OVER
()":


SELECT 
  id, name, similarity(name, 'Tooneyvara') as delta, 
  count (id) OVER() AS cnt
  FROM vtown
  WHERE
      similarity(name, 'Tooneyvara') > 0.1
  ORDER BY  delta DESC

produces result:
1787	Toomyvara	0.5	4
1787	Toomevara	0.4	4
1700	Ardcroney	0.105	4
1788	Townsfield	0.1	4

What I would like is a "3" in the cnt column (ignoring id duplicates).


How can I achieve this?


The best I can come up with is:

SELECT id, name,delta,  count (*) OVER() 
FROM (
SELECT DISTINCT ON (id)
  id, name, similarity(name, 'Tooneyvara') as delta 
  FROM vtown
  WHERE
    similarity(name, 'Tooneyvara') > 0.1
    ORDER BY id, delta DESC
) AS x
ORDER by delta DESC 

-- 

Best Regards,
Tarlika Elisabeth Schmitz

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general