Query to remove the duplicates from SQL

Question

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

Answer 1

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.