count (DISTINCT field) OVER ()

From: Tarlika Elisabeth Schmitz <postgresql6(at)numerixtechnology(dot)de>
To: pgsql-general(at)postgresql(dot)org>
Subject: count (DISTINCT field) OVER ()
Date: 2011-11-09 23:52:35
Message-ID: 20111109235235.1c57b344@dick.coachhouse
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-11-10 00:40:49 Re: count (DISTINCT field) OVER ()
Previous Message Emanuel Calvo 2011-11-09 23:24:29 Re: Weird waste time when UNION ALL to an empty result set