Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 147E91981822 for ; Tue, 8 Nov 2011 06:18:32 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 08399-10 for ; Tue, 8 Nov 2011 10:18:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from zebedee.cranntara.net (zebedee.cranntara.net [91.186.7.79]) by mail.postgresql.org (Postfix) with ESMTP id A68D71981821 for ; Tue, 8 Nov 2011 06:18:25 -0400 (AST) Received: from [88.210.164.29] (helo=lolek.coachhouse) by zebedee.cranntara.net with esmtpsa (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.72) (envelope-from ) id 1RNil4-0005Dz-3m for pgsql-sql@postgresql.org; Tue, 08 Nov 2011 10:18:23 +0000 Received: from dick.coachhouse ([192.168.1.32] ident=tarlika) by lolek.coachhouse with esmtp (Exim 4.72) (envelope-from ) id 1RNiTH-0000XH-5b for pgsql-sql@postgresql.org; Tue, 08 Nov 2011 09:59:59 +0000 Date: Tue, 8 Nov 2011 09:59:58 +0000 From: Tarlika Elisabeth Schmitz Cc: pgsql-sql@postgresql.org Subject: Re: GROUP and ORDER BY Message-ID: <20111108095958.2b6f3ad9@dick.coachhouse> In-Reply-To: <4EB8AF9C.7060305@comodo.com> References: <20111107212031.4d6310aa@dick.coachhouse> <4EB8AF9C.7060305@comodo.com> X-Mailer: Claws Mail 3.5.0 (GTK+ 2.20.1; i486-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.02 tagged_above=-5 required=5 tests=BAYES_40=-0.001, MISSING_HEADERS=1.021 X-Spam-Level: * X-Archive-Number: 201111/33 X-Sequence-Number: 36006 On Tue, 08 Nov 2011 09:57:08 +0530 Robins Tharakan 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.