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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general