CODEWITHSUNDEEP
mysql
Aakash Martand
Aakash Martand

Reputation: 944

Mysql get unique row

I have a table like this:

+----+-----------+---------+----------+
| id | from_city | to_city | distance |
+----+-----------+---------+----------+
|  1 | Ajmer     | Jodhpur |      200 |
|  2 | Jodhpur   | Ajmer   |      200 |
|  3 | Jaipur    | Jodhpur |      300 |
|  4 | Jodhpur   | Jaipur  |      300 |
|  5 | Kota      | Udaipur |      300 |
|  6 | Udaipur   | Kota    |      300 |
|  7 | Jaipur    | Ajmer   |      100 |
|  8 | Ajmer     | Jaipur  |      100 |
+----+-----------+---------+----------+

Now as from above table, id 1 and 2 are similar. So I'm trying to get the output like

+-----------+---------+----------+
| from_city | to_city | distance |
+-----------+---------+----------+
| Ajmer     | Jodhpur |      200 |
| Jaipur    | Jodhpur |      300 |
| Kota      | Udaipur |      300 |
| Jaipur    | Ajmer   |      100 |
+-----------+---------+----------+

I've tried UNION, and self join but didn't succeded.
Please help.

Upvotes: 0

Views: 40

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

If you don't care about the order of the from city and to city then

with cte as
(select id,to_city,from_city,distance,
case when to_city < from_city then to_city else from_city end fromcity,
case when to_city > from_city then to_city else from_city end tocity
from t
)
select distinct fromcity,tocity,distance from cte;

where the to and from city are shuffled in the cte.

drop table if exists t;

create table t
( id  int,from_city varchar(10), to_city varchar(10), distance int);
insert into t values
(  1 ,'Ajmer'     ,'Jodhpur' ,     200 ),
(  2 ,'Jodhpur'   ,'Ajmer'   ,     200 ),
(  3 ,'Jaipur'    ,'Jodhpur' ,     300 ),
(  4 ,'Jodhpur'   ,'Jaipur'  ,     300 ),
(  5 ,'Kota'      ,'Udaipur' ,     300 ),
(  6 ,'Udaipur'   ,'Kota'    ,     300 ),
(  7 ,'Jaipur'    ,'Ajmer'   ,     100 ),
(  8 ,'Ajmer'     ,'Jaipur'  ,     100 ); 

+----------+---------+----------+
| fromcity | tocity  | distance |
+----------+---------+----------+
| Ajmer    | Jodhpur |      200 |
| Jaipur   | Jodhpur |      300 |
| Kota     | Udaipur |      300 |
| Ajmer    | Jaipur  |      100 |
+----------+---------+----------+
4 rows in set (0.001 sec)

Upvotes: 1

Related Questions