Thread: GROUP and ORDER BY
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
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?
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.
Hmmm... Missed that! I think you are looking for the feature that was introduced in PostgreSQL 9.1 where you could have a non-group-by column in the select list, but only if the group-by has a pkey to identify the actual row. http://www.postgresql.org/docs/9.1/static/release-9-1.html (Search for GROUP BY) -- Robins Tharakan On 11/08/2011 03:29 PM, Tarlika Elisabeth Schmitz wrote: > 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.