How to order by joined column

[Mike-the-one]

Here is my db, table A has user orders, and table B has the ordered items, and table C has products.

One order can have multiple items.

Table B has foreign key to order id (A.id), and table B also has foreign key to C.id (product id).

I am using datatable to list orders, and one column is orderItems (which maps to A.orderItems).

Now I would like to sort the result by product SKU.

I am using AdditionalSpecification. I added query.orderBy but I am facing this error

expression #1 of ORDER BY clause is not in SELECT list, references column 'db.p_.p_sku' which is not in SELECT list; this is incompatible with DISTINCT

The suggested way to fix this is 1: remove ONLY_FULL_GROUP_BY in sql_mode, which I don't want to do. 2: use ANY_VALUE or MAX or MIN.

I am trying it with MAX, now I got error

Expression #1 of ORDER BY contains aggregate function and applies to the result of a non-aggregated query

So the SELECT statement does not include P.sku. How can I add it to the SELECT?

The tricky part is, order can have multiple orderItems, and each orderItem connects to one product.

Thanks