How to properly MIN and MAX in mysql?-CodePudding

I have the following query:

SELECT DISTINCT 

MIN(re1.name, re2.name) AS name1, 
       MAX(re1.name, re2.name) AS name2


FROM Rating ra1 JOIN Reviewer re1
ON re1.rID = ra1.rID
CROSS JOIN
Reviewer re2 JOIN Rating ra2 ON re2.rID = ra2.rID
WHERE ra1.mID = ra2.mID
AND re1.rID != re2.rID
AND re1.rID > re2.rID
ORDER BY name1, name2;

When I run the query in a learning platform I'm currently using (that uses sqlite), it computes a list of coupled names order alphabetically.

However, if I run the same query in vscode using mysql Ver 8.0.26 for Linux on x86_64 I got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', re2.name) AS name1, MAX(re1.name, re2.name) AS name2 FROM Rating ra1' at line 3

Is MIN MAX in mysql used differently? How do I run the same query using mysql?

Thanks

CodePudding user response：

MAX() is for checking in the same column, aggregated across multiple rows. I think you want the GREATEST() function.