Am 20.05.2012 05:04, schrieb Jasen Betts:
On 2012-05-19, Andreas<maps...@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 :)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
