How to remove two duplicate column

Question

i have some table like:
Sql Fiddle

I want this table to select only the following values(remove duplicates):
from | to
46 0
46 18
46 50
46 39
46 11
46 12
46 56

i use DISTINCT keyword,but this return folowing values:

46 0
46 18
46 50
46 39
46 11
11 46
46 12
46 56

please help me

Please show the query that you are using and give the tables names. If possible an http://sqlfiddle.com/ would be appreciated. — skiwi, Dec 07 '13 at 13:34
Duplicate? http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Maciej Sz, Dec 07 '13 at 13:35
It is not quite clear. Is `from` and `to` in your example two different tables or two columns of one table? — Alexander Myshov, Dec 07 '13 at 13:40
@AlexanderMyshov two columns of the same table as far as i can see. — bansi, Dec 07 '13 at 13:41

score 3 · Answer 1 · answered Dec 07 '13 at 13:35

3

SELECT  DISTINCT a.*
FROM    TableName a
        INNER JOIN TableName b
          ON a.from = b.to
             AND a.to = b.from
WHERE   a.from < b.from

SQLFiddle Demo

answered Dec 07 '13 at 13:35

John Woo

258,903
69
498
492

score 1 · Answer 2 · answered Dec 08 '13 at 10:10

Here is my solution, it seems not so ideal, but it works:

select distinct pm.`from`, pm.`to`
from `tsk_private_message` pm
left join
    (select distinct pm.`from`, pm.`to`
     from `tsk_private_message` pm
         inner join `tsk_private_message` pm2
         on (pm.`to` = pm2.`from`) and (pm2.`to` <> pm.`from`)) a
     using (`from`, `to`)
where a.`from` is null;

In this query I just search unnecessary rows among these conversations via subquery:

select distinct pm.`from`, pm.`to`
from `tsk_private_message` pm
    inner join `tsk_private_message` pm2
    on (pm.`to` = pm2.`from`) and (pm2.`to` <> pm.`from`)) a
using (`from`, `to`)

and "subtract" this result from main table.

Here is SQL Fiddle

How to remove two duplicate column

2 Answers2

Linked

Related