Re: count (DISTINCT field) OVER ()

Tarlika Elisabeth Schmitz, 10.11.2011 11:24:
>> 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
>>
>
>
> I like you suggestion, Thomas. It is not that dissimilar from but
> cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id)
> attempt.
> It's also very slightly faster.
>
> Here's another, slightly shorter, variation of your suggestion:
>
> SELECT id, name, delta,
> max(rank) OVER() as cnt
> FROM (
> SELECT
> id, name, similarity(name, 'Tooneyvara') as delta,
> dense_rank() OVER(ORDER BY id) AS rank
> FROM vtown
> WHERE
> similarity(name, 'Tooneyvara')> 0.1
> ) t
> ORDER BY delta DESC

Nice trick with the dense_rank(), never thought of that.

Regards
Thomas