On Nov 9, 2011, at 18:52, Tarlika Elisabeth Schmitz <postgres...@numerixtechnology.de> wrote:
> 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general 1. Write your main query in a WITH (CTE) 2. Query #1 with appropriate GROUP BY clause (CTE) 3. In the main statement JOIN 1 and 2 David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
