CODEWITHSUNDEEP
mysqlsqlduplicatesdistinct
shiva
shiva

Reputation: 89

Query to remove the duplicates from SQL

I have on table called distance. It has 4 columns. id, start_from, end_to and distance.

I have some duplicate records. Duplicate records in the sense,

start_from   |   end_to    | distance
Chennai        Bangalore     350
Bangalore      Chennai       350
Chennai        Hyderabad     500
Hyderabad      Chennai       510

In above table, chennai to bangalore and bangalore to chennai both have same distance. So I need query to remove that record on select.

I want a out put like

start_from   |   end_to    | distance
Chennai        Bangalore     350
Chennai        Hyderabad     500
Hyderabad      Chennai       510

Upvotes: 5

Views: 1344

Answers (4)

Blank
Blank

Reputation: 12378

If there is no different between Chennai to Bangalore or Bangalore to Chennai, you can try this:

select
    max(`start_from`) as `start_from`,
    min(`end_to`) as `end_to`,
    `distance`
from yourtable
group by
    case when `start_from` > `end_to` then `end_to` else `start_from` end,
    case when `start_from` > `end_to` then `start_from` else `end_to` end,
    `distance`

Here is a demo in rextester.

Even if Chennai to Hyderabad is 350 also works demo.

And if you want Bangalore to Chennai to be remained, you can just change the place of max and min:

select
    min(`start_from`) as `start_from`,
    max(`end_to`) as `end_to`,
    `distance`
from yourtable
group by
    case when `start_from` > `end_to` then `end_to` else `start_from` end,
    case when `start_from` > `end_to` then `start_from` else `end_to` end,
    `distance`

also a demo.

And case when will be compatible to most databases.

Upvotes: 2

鄭有維
鄭有維

Reputation: 265

Assume your Table like

id  start_from              end_to                  distance
0   Chennai                 Bangalore               350
1   Bangalore               Chennai                 350
2   Chennai                 Hyderabad               500
3   Hyderabad               Chennai                 510

Then you can use the query compare with id.

Select 
    O.start_from,
    O.end_to,
    O.distance 
From 
    distance O
Left Join
    distance P
On 
    1 = 1
    and O.start_from = P.end_to 
    and O.end_to = P.start_from
Where 
    1 = 1
    and O.distance <> P.distance 
    or(O.distance = P.distance and O.id < P.id)

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use the following query to find the duplicates:

SELECT LEAST(start_from, end_to) AS start_from, 
       GREATEST(start_from, end_to) AS end_to, 
       distance
FROM mytable 
GROUP BY LEAST(start_from, end_to), GREATEST(start_from, end_to), distance
HAVING COUNT(*) > 1

Output:

start_from,   end_to,  distance
--------------------------------
Bangalore,    Chennai, 350

Now you can use the above query as a derived table to filter out the duplicates:

SELECT t1.*
FROM mytable AS t1
LEFT JOIN (
    SELECT LEAST(start_from, end_to) AS start_from, 
           GREATEST(start_from, end_to) AS end_to, 
           distance
    FROM mytable 
    GROUP BY LEAST(start_from, end_to), GREATEST(start_from, end_to), distance
    HAVING COUNT(*) > 1
) AS t2 ON t1.start_from = t2.start_from AND 
           t1.end_to = t2.end_to AND 
           t1.distance = t2.distance    
WHERE t2.start_from IS NULL

The WHERE clause predicate, t2.start_from IS NULL, filters out duplicate records.

Output:

start_from  end_to     distance
--------------------------------
Chennai     Bangalore  350
Chennai     Hyderabad  500
Hyderabad   Chennai    510

Upvotes: 2

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4294

Setting the field order (using values) in query helps to get an unique row: 

select distinct
    case when start_from  > end_to then end_to     else  start_from end as _start,
    case when start_from  > end_to then start_from else  end_to     end as _end,
    distance
from distance;

After a test I get:

+-----------+-----------+----------+
| _start    | _end      | distance |
+-----------+-----------+----------+
| Bangalore | Chennai   |      350 |
| Chennai   | Hyderabad |      500 |
| Chennai   | Hyderabad |      510 |
+-----------+-----------+----------+

Upvotes: 0

Related Questions