Message-ID: <4FB8B489.3050701@gmx.net>
Date: Sun, 20 May 2012 11:08:25 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
To: Jasen Betts <jasen@xnet.co.nz>
CC: pgsql-sql@postgresql.org
Subject: Re: Need help in grouping records
References: <4FB7EEC6.1030004@gmx.net> <jp9mvc$qmu$1@reversiblemaps.ath.cx>
In-Reply-To: <jp9mvc$qmu$1@reversiblemaps.ath.cx>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Am 20.05.2012 05:04, schrieb Jasen Betts:
> On 2012-05-19, Andreas<maps.on@gmx.net>  wrote:
>> Hi,
>>
>> I'm trying to fight against double entries in tables.
>> I got as far as I can find similar records with trigram string matching.
>> If I do this with a table compared to itself I get something like this:
>>
>> id_a, id_b
>> 3,   5
>> 3,   7
>> 5,   3
>> 5,   7
>> 7,   3
>> 7,   5
>> 11,   13
>> 13,   11
>>
>> so the records with the IDs 3, 5, 7 and 11, 13 are similar enough to
>> form a group.
>>
>> How would I get a list of record-IDs with a group-ID like this
>>
>> record_id, group_id
>> 3,   1
>> 5,   1
>> 7,   1
>> 11,   2
>> 13,   2
>>
>> Is there a way to get this by SQL ?
>
>    select id_a,min(least(id_a,id_b)) from SOMETHING group by id_a
>
> close enough?
>
> or this: ?
>
>    select id_a, rank() over order by g  from
>    ( select id_a,min(least(id_a,id_b)) as g from SOMETHING group by id_a ) as foo
>
>
>
Thanks   :)
Thats by far more elegant as my approach with arrays I figured out in 
the meantime.

I changed rank() to dense_rank() in your solution.

Functionally the 1st line does allready all the magic, though.

Great   :)