mysql - After using any_value also getting error Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

Question

I am using group by clause in mysql query and getting error of nonaggregated column,I can't disable full_ group_by from preveliges because I am using share hosting so don't have access to this so I tried alternate way ANY_VALUE however using this also I am getting same error: Mysql query

SELECT any_value(o.booking_id), any_value(o.id)id,any_value(u.name) as user_name,  
FROM orders o 
    JOIN items ON items.id=o.product_id 
    JOIN users u ON u.id=o.user_id 
    JOIN payment p ON p.booking_id=o.booking_id 
WHERE o.status =0 
GROUP BY o.booking_id 
ORDER BY o.id DESC 
LIMIT 10

question from:https://stackoverflow.com/questions/65886776/after-using-any-value-also-getting-error-expression-1-of-order-by-clause-is-not

Answer 1

Your query does not select the column o.id so you can't sort by o.id.

But it does select and return the column any_value(o.id) which you can use:

ORDER BY any_value(o.id)

or its alias:

ORDER BY id

Also there is no need to use any_value() for the column(s) that are included in the GROUP BY clause:

SELECT o.booking_id, any_value(o.id)id,any_value(u.name) as user_name 
FROM orders o 
    JOIN items ON items.id=o.product_id 
    JOIN users u ON u.id=o.user_id 
    JOIN payment p ON p.booking_id=o.booking_id 
WHERE o.status =0 
GROUP BY o.booking_id 
ORDER BY id DESC 
LIMIT 10