pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help in grouping records
3+ messages / 2 participants
[nested] [flat]
* Need help in grouping records
@ 2012-05-19 19:04 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2012-05-19 19:04 UTC (permalink / raw)
To: pgsql-sql
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 ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help in grouping records
@ 2012-05-20 03:04 Jasen Betts <jasen@xnet.co.nz>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Jasen Betts @ 2012-05-20 03:04 UTC (permalink / raw)
To: pgsql-sql
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
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need help in grouping records
@ 2012-05-20 09:08 Andreas <maps.on@gmx.net>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 3+ messages in thread
From: Andreas @ 2012-05-20 09:08 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
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 :)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-05-20 09:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-19 19:04 Need help in grouping records Andreas <maps.on@gmx.net>
2012-05-20 03:04 ` Jasen Betts <jasen@xnet.co.nz>
2012-05-20 09:08 ` Andreas <maps.on@gmx.net>
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