Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

MySQL Swap/Sort Values in same row of SELECT RESULTS (NOT UPDATING TABLE)

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 ...
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading