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
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 |