Skip site navigation (1) Skip section navigation (2)

Re: Need help in grouping records

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 (view raw or flat)
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



In response to

Responses

pgsql-sql by date

Next:From: AndreasDate: 2012-05-20 09:08:25
Subject: Re: Need help in grouping records
Previous:From: AndreasDate: 2012-05-19 19:04:38
Subject: Need help in grouping records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group