CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-byleft-join
Fibericon
Fibericon

Reputation: 5793

ORDER BY not working as expected with GROUP BY

I have two tables, establishment and branch. Establishment is the parent table. When I select a list of branches, I want it to retrieve the establishment name if the branch title is blank. Also, I only want to show a branch once for each establishment. This is what I came up with:

SELECT 
  branch.id
  , branch.establishment_ID
  , IFNULL(branch.branch_title, establishment.name) AS branch_title 
FROM branch 
LEFT JOIN establishment ON branch.establishment_ID = establishment.id 
WHERE cityID = 2 
GROUP BY establishment_ID 
ORDER BY branch_title

However, the results do not appear to be in any particular order. I want them to be in alphabetical order. I've read that MySQL 5.0.5 had problems with GROUP BY and ORDER BY in the same query, but I'm working with 5.5.9. What can I do to fix this query?

Upvotes: 1

Views: 2195

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

I would guess (knowing other SQL systems) that your ORDER BY is applying to branch.branch_title, rather than to your new alias. Are you able to copy your IFNULL() expression into the ORDER BY?

SELECT 
  branch.id
  , branch.establishment_ID
  , IFNULL(branch.branch_title, establishment.name) AS branch_title 
FROM branch 
LEFT JOIN establishment ON branch.establishment_ID = establishment.id 
WHERE cityID = 2 
GROUP BY establishment_ID 
ORDER BY IFNULL(branch.branch_title, establishment.name)

Otherwise, the NULL branch_title rows will always appear (before/after) the non-NULL ones, and will not be sorted in any way.

Upvotes: 1

Related Questions