sql - MySQL orders results randomly despite ORDER BY clause

Question

We're using the following SQL statement:

SELECT billing_invoice.id as 'billing_invoice.id' 
from billing_invoice 
inner join client_billing_invoice on client_billing_invoice.client_id = '1337' 
where billing_invoice.company_id = '7c701774-5046-4b9a-9a82-b0e9cac59794' or client_billing_invoice.client_id = '1337' 
group by billing_invoice.id 
order by billing_invoice.timestamp_created DESC limit 0,5

However it's randomly ordering the results despite the order and group by clauses. Is there's anything that I am missing here?

question from:https://stackoverflow.com/questions/65904791/mysql-orders-results-randomly-despite-order-by-clause

Answer 1

This logic:

select billing_invoice.id
from . . .
group by billing_invoice.id
order by billing_invoice.timestamp_created desc 

should return an error. Why? The order by is executed after the aggregation and there is no billing_invoice.timestamp_created after the GROUP BY.

This code should be generating a syntax error. And it would in the more recent versions of MySQL (with the default settings).

You need an aggregation function:

order by max(billing_invoice.timestamp_created) desc