Tarlika Elisabeth Schmitz, 10.11.2011 00:52:
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).
This should do it:
SELECT id,
name,
delta,
sum(case when rn = 1 then rn else null end) over() as distinct_id_count
FROM (
SELECT
id, name, similarity(name, 'Tooneyvara') as delta,
row_number() OVER(partition by id) AS rn
FROM vtown
WHERE
similarity(name, 'Tooneyvara') > 0.1
) t
ORDER BY delta DESC
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
