Unless I overlooked something here, does this work ? SELECT no, name, MAX(similarity(name, 'Tooneyvara')) AS sim FROM vtown WHERE similarity(name, 'Tooneyvara') > 0.4 GROUP BY no, name ORDER BY sim DESC
-- Robins Tharakan On 11/08/2011 02:50 AM, Tarlika Elisabeth Schmitz wrote:
Hello, I would like to GROUP the result by one column and ORDER it by another: SELECT no, name, similarity(name, 'Tooneyvara') AS s FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.4 ORDER BY s DESC Result: 1787 "Toomyvara" 0.5 1787 "Toomevara" 0.4 1188 "Toonybara" 0.4 Desired result: 1787 "Toomyvara" 0.5 1188 "Toonybara" 0.4 Gets rid of the duplicate "no" keeping the spelling with the greater similarity and presents the remaining result ordered by similarity. My solution: SELECT * FROM ( SELECT DISTINCT ON (no) no, name, similarity(name, 'Tooneyvara') AS sim FROM vtown WHERE similarity(name, 'Tooneyvara')> 0.4 ORDER BY no, sim DESC ) AS x ORDER BY sim Is that the best way to achieve this result?
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql