pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGROUP and ORDER BY
4+ messages / 2 participants
[nested] [flat]
* GROUP and ORDER BY
@ 2011-11-07 21:20 Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-11-07 21:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: GROUP and ORDER BY
@ 2011-11-08 04:27 Robins Tharakan <robins.tharakan@comodo.com>
parent: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
0 siblings, 1 reply; 4+ messages in thread
From: Robins Tharakan @ 2011-11-08 04:27 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: GROUP and ORDER BY
@ 2011-11-08 09:59 Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
parent: Robins Tharakan <robins.tharakan@comodo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tarlika Elisabeth Schmitz @ 2011-11-08 09:59 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: GROUP and ORDER BY
@ 2011-11-08 15:03 Robins Tharakan <robins.tharakan@comodo.com>
parent: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
0 siblings, 0 replies; 4+ messages in thread
From: Robins Tharakan @ 2011-11-08 15:03 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-11-08 15:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-07 21:20 GROUP and ORDER BY Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
2011-11-08 04:27 ` Robins Tharakan <robins.tharakan@comodo.com>
2011-11-08 09:59 ` Tarlika Elisabeth Schmitz <postgresql6@numerixtechnology.de>
2011-11-08 15:03 ` Robins Tharakan <robins.tharakan@comodo.com>
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