SQL OrderBy Clause Not Working with max alias

user_9565558 0
Viewed 43

I have a query

Select tableA.*,  max(`queue_position`) AS `product_advertisements.queue_position` 
from tableA join tableB
group by tableA.attribute
order by `product_advertisements.queue_position` ASC

When the query runs, its not actually sorting by the queue position,

This is my return https://gyazo.com/471749b349d64e94707b9452d4b25417, This is the expected output, https://gyazo.com/e8c92b2898a404781c9de095f1146143

Does it have something to do with ordering by the joined attribute? Why is my order by clause being ignored completely

mysql sql
1 Answers

I do not know the name of all columns, but I guess there is a column with the same name "product_advertisements.queue_position", and you do not want to order by it. Instead, you want to order by MAX(queue_position)

Try these 2 options:

  1. Use the MAX column as the first column, and "ORDER BY 1", it would apply the order clause to the first column.

    Select max(queue_position) AS product_advertisements.queue_position, tableA.* from tableA join tableB group by tableA.attribute order by 1 ASC;

  2. Use "max(queue_position)" in the ORDER CLAUSE

    Select tableA.*, max(queue_position) AS product_advertisements.queue_position from tableA join tableB group by tableA.attribute order by max(queue_position) ASC

Anyway, why do you select all columns (tableA.*), but group only by one? MySQL accepts it, but in other Databases it is not allowed.

user_164948860
Related