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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
