Remove almost duplicate rows

Question

I have an Sqlite3 database with a table like this:

Table(com1, com2)

A || B
B || A
C || D
D || B
B || D

If I have 2 rows: A || B, and B || A, I want to delete one of them (I don't care which one).

So to obtain:

A || B
C || D
D || B

I've read the many asks about duplicate rows but I cant find something like this. Thanks for any help.

It will be easier to help you with your code if you post at least an attempt of your own. — ghoti, Nov 03 '12 at 13:00

score 0 · Accepted Answer · edited Mar 04 '13 at 18:04

0

I think solution for your problem should look like this:

SELECT
    t1.val1,t1.val2
FROM table AS t1
JOIN table AS t2 ON (
    (t1.val1=t2.val2) AND (t1.val2=t2.val1)
) WHERE t1.val1<=t1.val2

where table is the name of your table and val1 and val2 are names of columns in that table.

edited Mar 04 '13 at 18:04

halfer

answered Nov 03 '12 at 13:00

MichalHlavacek

sqlite3 says:Error: no such function: strcmp – SomeoneMe Nov 03 '12 at 13:17
I substituted WHERE strcmp(t1.val1, t1.val2)<=0 with WHERE t1.val1 <= t1.val2, and it works – SomeoneMe Nov 03 '12 at 13:32
Sorry about that, I fixed it now...It should work (just tested on my side). Datatypes are tricky in sqlite :-) – MichalHlavacek Nov 03 '12 at 13:36

1 Answers1