From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Need help in grouping records |
Date: | 2012-05-20 03:04:12 |
Message-ID: | jp9mvc$qmu$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
--
⚂⚃ 100% natural
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2012-05-20 09:08:25 | Re: Need help in grouping records |
Previous Message | Andreas | 2012-05-19 19:04:38 | Need help in grouping records |