Re: count (DISTINCT field) OVER ()

Thomas Kellerer <spam_eater@xxxxxxx> · Thu, 10 Nov 2011 11:45:22 +0100

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general