Mysql get unique row

Question

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.

What have you tried so far? Where are you stuck? Is there any rule behind the given output? — Nico Haase, Aug 13 '20 at 12:36
Duplicate. please refer this. https://stackoverflow.com/questions/1841426/is-it-possible-to-group-by-multiple-columns-using-mysql — Fir3, Aug 13 '20 at 12:40
Share your attempts, and whichever rule stands behind the expected output. Please do this by editing your question — Nico Haase, Aug 13 '20 at 12:40
@YasasNaveen would you please guide me on how this problem can be solved by using `GROUP BY`? — Aakash Martand, Aug 13 '20 at 12:42

score 1 · Accepted Answer · answered Aug 13 '20 at 12:56

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)

Mysql get unique row

1 Answers1