Re: count (DISTINCT field) OVER () - Mailing list pgsql-general

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