count (DISTINCT field) OVER ()
От | Tarlika Elisabeth Schmitz |
---|---|
Тема | count (DISTINCT field) OVER () |
Дата | |
Msg-id | 20111109235235.1c57b344@dick.coachhouse обсуждение исходный текст |
Ответы |
Re: count (DISTINCT field) OVER ()
Re: count (DISTINCT field) OVER () |
Список | pgsql-general |
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). How can I achieve this? The best I can come up with is: SELECT id, name,delta, count (*) OVER() FROM ( SELECT DISTINCT ON (id) id, name, similarity(name, 'Tooneyvara') as delta FROM vtown WHERE similarity(name, 'Tooneyvara') > 0.1 ORDER BY id, delta DESC ) AS x ORDER by delta DESC -- Best Regards, Tarlika Elisabeth Schmitz
В списке pgsql-general по дате отправления: