Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql ORDER BY or MAX() for several table fields orderring?

I've mixed up with a strange behavior of MySQL query. I have next 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 

By the idea, it has to order 10 rows in descending method depending on how many values fields have in ORDER BY condition. But in the result I get the next one:

kate 103
pete 101
steve 102

instead of

kate 103
steve 102
pete 101

Does anyone know why it set incorrect order? And what to do in order to make the proper ORDER BY DESC condition?

Is it possible to use MAX() for several fields? If yes, maybe it is possible to organize the MySQL query like this?

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
like image 692
ilnur777 Avatar asked Jun 07 '11 19:06

ilnur777


People also ask

How do I sort multiple columns in MySQL?

To sort the records in descending order, use the DESC keyword. Syntax: SELECT * FROM table_name ORDER BY column_name; For Multiple column order, add the name of the column by which you'd like to sort records first.

Does MySQL allow the use of ORDER BY and limit in the same query?

In MySQL, the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments that are offset and count. The value of both the parameters can be zero or positive integers.

How insert multiple orders MySQL?

Using with multiple columnsDefine your multiple column names in ORDER BY clause separated by a comma (,). You can also specify your sorting order ASC or DESC . In the above query, I am ordering the emp_salary table by age in Ascending order and salary by descending order.

Which clause of MySQL helps to arrange records in an order?

The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.


1 Answers

You can't use MAX() because that function returns the largest value in one column over many rows. But you can use MySQL's GREATEST() function. This returns the largest of its arguments, which all come from one row.

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

But I would also comment that you have violated 1st Normal Form by storing multiple columns that should be the same "kind" of data. The consequences are that you face this troublesome query. Also you'll need to rewrite all your queries when you add a fifth field.

Instead, create a second table and store all the "fields" in a single column, with a reference to the user. Then you can join the tables:

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

Or my favorite way of getting the row with the greatest value per group:

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
like image 196
Bill Karwin Avatar answered Oct 19 '22 12:10

Bill Karwin