On Tue, 08 Nov 2011 09:57:08 +0530 Robins Tharakan <robins.thara...@comodo.com> wrote:
>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. >> >>[...] >[...] 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 Thank you for yuor suggestion, Robins. Unfortunately, it does not work; this returns: 1787 "Toomyvara" 0.5 1787 "Toomevara" 0.4 1188 "Toonybara" 0.4 because while column "no" is identical, "name" isn't and you're grouping by both of them. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
