Join with Group By & Order by using Case in MySQL

Question

Why this query wont work? Is it beacause combinaton of order by and group by?

One table is with adverts, other with subscriptions, third is with services, and fourth is many to many relation between services and locations (location is position where advert should be shown).

What i want is to order adverts stored in adverts table having location 2 first, then those who don't have location defined and then with location 1 (this order is generated programmicaly)

adverts table:
id, name, subscription_id

subscriptions table:
subscription_id, service_id, date, paid etc...

service_locations table:
service_id, location_id

as you can se there is fourth table in this case, but it is unimportant

The query:

select adverts.id, GROUP_CONCAT(service_locations.location_id) AS locations from adverts 
    left join subscriptions 
        on adverts.subscription_id = subscriptions.id
    left join service_locations 
        on service_locations.service_id = subscriptions.service_id
    group by adverts.id
    order by case service_locations.location_id 
        when 2 then 1 
        when 1 then 3 
        else 2 
    end

Expected results:

+----+-----------+
| id | locations |
+----+-----------+
|  1 | 2         |
|  3 | 1,2       |
|  2 | null      |
+----+-----------+

What i actually get (the third in row has location 2 but it is placed after null):

+----+-----------+
| id | locations |
+----+-----------+
|  1 | 2         |
|  2 | null      |
|  3 | 1,2       |
+----+-----------+

score 0 · Answer 1 · answered Feb 28 '16 at 15:30

When you use group by, all columns not in the group by should have aggregation functions. So, I think you intend something like this:

select a.id, GROUP_CONCAT(sl.location_id) AS locations
from adverts a left join
     subscriptions s
     on a.subscription_id = s.id left join
     service_locations sl
     on sl.service_id = s.service_id
group by a.id
order by max(case sl.location_id 
               when 2 then 1 
               when 1 then 3 
               else 2 
             end);

I'm not sure if max() is what you really need, but you do need an aggregation function. This specifically produces the output in the question:

order by (case min(sl.location_id)
               when 2 then 1 
               when 1 then 2
               else 3
           end);

score 0 · Answer 2 · edited May 23 '17 at 12:31

I have found a solution, order by must be executed before group by, which is not a default behaivor, more about that behaivour here: https://stackoverflow.com/a/14771322/4329156) (a subquery must be used)

So, query should look like

select *, GROUP_CONCAT(location_id) as locations from (
    select adverts.id AS id, service_locations.location_id AS location_id from adverts 
    left join subscriptions 
        on adverts.subscription_id = subscriptions.id
    left join service_locations 
        on service_locations.service_id = subscriptions.service_id
    order by case service_locations.location_id 
        when 2 then 1 
        when 1 then 3 
        else 2 
    end
    ) as table 
    group by table.id
    order by case table.location_id 
        when 2 then 1 
        when 1 then 3 
        else 2 
    end

Join with Group By & Order by using Case in MySQL

2 Answers2