From: Robins Tharakan <robins.tharakan@comodo.com>
To: pgsql-sql@postgresql.org
Cc: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
Subject: Re: GROUP and ORDER BY
Date: Tue, 08 Nov 2011 09:57:08 +0530
Message-ID: <4EB8AF9C.7060305@comodo.com> (raw)
In-Reply-To: <20111107212031.4d6310aa@dick.coachhouse>
References: <20111107212031.4d6310aa@dick.coachhouse>
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?
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: robins.tharakan@comodo.com, postgresql6@numerixtechnology.de
Subject: Re: GROUP and ORDER BY
In-Reply-To: <4EB8AF9C.7060305@comodo.com>
* 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