Date: Mon, 7 Nov 2011 21:20:31 +0000
From: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: GROUP and ORDER BY
Message-ID: <20111107212031.4d6310aa@dick.coachhouse>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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?
-- 

Best Regards,
Tarlika Elisabeth Schmitz