Re: GROUP and ORDER BY

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?