Re: count (DISTINCT field) OVER ()

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