From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Need help in grouping records |
Date: | 2012-05-20 09:08:25 |
Message-ID: | 4FB8B489.3050701@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Am 20.05.2012 05:04, schrieb Jasen Betts:
> On 2012-05-19, Andreas<maps(dot)on(at)gmx(dot)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 :)
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Bakuwel | 2012-05-20 23:17:04 | master/detail |
Previous Message | Jasen Betts | 2012-05-20 03:04:12 | Re: Need help in grouping records |