Geek asks and answers

Mysql ORDER BY or MAX () to arrange multiple table fields?

I messed up with the strange behavior of the MySQL query. I have the following mysql query:

SELECT 'username','status', 'field_1', 'field_2', 'field_3', 'field_4',  
    FROM my_table 
    ORDER by field_1 DESC, field_2 DESC, field_3 DESC, field_4 DESC 
    LIMIT 0,10

In theory, he should order 10 rows in the descending method, depending on how many value fields the ORDER BY clause has. But as a result, I get the following:

Kate 103
pete 101
steve 102

instead

Kate 103
Steve 102 Pete
101

Does anyone know why he set the wrong order? And what to do to fulfill the proper ORDER BY DESC condition?

Is it possible to use MAX () for multiple fields? If so, is it possible to organize such a MySQL query?

SELECT 'username','status', 'field_1', 'field_2', 'field_3', 'field_4', MAX(field_1,field_2,field_3,field_4) AS total 
    FROM my_table 
    ORDER by total DESC 
    LIMIT 0,10
+3
source share
2 answers

MAX(), . MySQL GREATEST(). , .

SELECT `username`, `status`, GREATEST(field_1,field_2,field_3,field_4) AS field_greatest
FROM my_table
ORDER BY field_greatest DESC
LIMIT 0,10

, 1st Normal Form, , "" . . , . .

"" . :

SELECT t.username, t.status, f.field
FROM my_table AS t
LEFT OUTER JOIN (SELECT username, MAX(field) AS field FROM my_fields GROUP BY username) AS f
  ON t.username = f.username
ORDER BY f.field DESC
LIMIT 0,10

:

SELECT t.username, t.status, f1.field
FROM my_table AS t
JOIN my_fields AS f1 ON t.username = f1.username
LEFT OUTER JOIN my_fields AS f2 ON t.username = f2.username AND f1.field < f2.field
WHERE f2.username IS NULL
ORDER BY f1.field DESC
LIMIT 0,10
+11

. IF(), .

SELECT `username`,`status`,  
MAX(
  if( field_1 > field_2 and field_1 > field_3 and field_1 > field_4 ,field_1, 
  if( field_2 > field_3 and field_2 > field_4 ,field_2,
  if( field_3 > field_4, field_3, field_4 )))) AS total 
FROM my_table 
ORDER by total DESC 
LIMIT 0,10
0

More articles:


All Articles