SQL, how to group rows based on field values

i have question about query result group. The image is a example. it is cable list. Each cable come with two attributes, ‘From’ location and ‘To’ location. if we’d like to group the cable list by location, it becomes tricky.

when we group the data by two locations, the results will land into two groups.

• ‘A->B’,
• ‘B->A’

But in reality, it makes more sense to combine these two groups into one, ‘From<->’, or saying cable list between two locations.

Think of add one more filed to mark cables between two locations. But didn’t come up with any idea.

thank you for sharing your thoughts.

regards, Roland

SQL, group

enter image description here

Answer

You can group by the least and the greatest of the 2 columns.
Use your database’s functions to do this.
The SQL standard is a CASE expression like:

GROUP BY CASE WHEN A < B THEN A ELSE B END,
         CASE WHEN A < B THEN B ELSE A END

Or maybe you can use a function like IF() or IIF():

GROUP BY IF(A < B, A, B),
         IF(A < B, B, A)

or functions like LEAST() and GREATEST():

GROUP BY LEAST(A, B),
         GREATEST(A, B)