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.
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.
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
GROUP BY IF(A < B, A, B), IF(A < B, B, A)
or functions like
GROUP BY LEAST(A, B), GREATEST(A, B)