I have retreived a result set. And the set is simply 3 rows (though it could be more in the future), and one of the rows (the last row), has the values sorted incorrectly. As such:
QUERY
select s1.`name` as place1, s2.`name` as place2
from locations s1
inner join locations s2 on s1.id < s2.id;
RESULT SET
| place1 | place2 |
|---|---|
| MyHouse | YourHouse |
| BobsHouse | LarrysHouse |
| TimmysHouse | AdamsHouse |
So, the correct result would be:
RESULT SET
| places1 | places2 |
|---|---|
| MyHouse | YourHouse |
| BobsHouse | LarrysHouse |
| AdamsHouse | TimmysHouse |
I have tried a million different combinations of JOINS, INNER_JOINS, UNIONS, using ON, USING, VALUES, and setting variables etc… and I simply cannot get this seemingly simple task completed
>Solution :
SELECT LEAST(s1.`name`, s2.`name`) as place1,
GREATEST(s1.`name`, s2.`name`) as place2
FROM ...