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
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With