From: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: GROUP and ORDER BY
Date: Tue, 8 Nov 2011 09:59:58 +0000
Message-ID: <20111108095958.2b6f3ad9@dick.coachhouse> (raw)
In-Reply-To: <4EB8AF9C.7060305@comodo.com>
References: <20111107212031.4d6310aa@dick.coachhouse>
<4EB8AF9C.7060305@comodo.com>
On Tue, 08 Nov 2011 09:57:08 +0530
Robins Tharakan <robins.tharakan@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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql6@numerixtechnology.de
Subject: Re: GROUP and ORDER BY
In-Reply-To: <20111108095958.2b6f3ad9@dick.coachhouse>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox