SQL error on a question that worked on Friday

Get Help
1

Hey!

I've got this question that I built a couple of years ago that I've been using fine since then up until today. Now it's giving an error and no changes were made to the syntax, so I don't know what happened. :grimacing: Any insight would be much appreciated.

image
image748×308 16.2 KB

with first_order as (
    select 
    client_id,
    status,
    service_id,
    min(starttime) as first_time 
    from 
    tb_orders
    where 
    starttime >= '2005-01-01' and status !=0 and status !=-1 and status !=-2 and service_id !=22 and service_id !=480 and service_id !=540 and service_id !=1503 and service_id !=895 and service_id !=896 and service_id !=1575 and service_id !=1604 and service_id !=1605 and service_id !=1065 and service_id !=1066 and service_id !=1067 and service_id !=1144 and service_id !=1727 and service_id !=1211 and service_id !=1212 and service_id !=1382 and service_id !=1427 and service_id !=1501 and service_id !=1431 and service_id !=1429 and service_id !=1500 and service_id !=1432 and service_id !=1502 and service_id !=1430 and service_id !=1499 and service_id !=1433 and service_id !=1434 and service_id !=1435 and service_id !=1527 and service_id !=1728 and service_id !=1865 and service_id !=1866 and service_id !=1867 and service_id !=1868 and service_id !=1869 and service_id !=1870 and service_id !=1871 and service_id !=1647 and service_id !=1680 and service_id !=1681 and service_id !=1682 and service_id !=1683 and service_id !=1708 and service_id !=1709
    group by client_id)
select client_id as client_id, first_time as first_order_date, MONTH(first_time) as first_month 
from first_order group by client_id
2

Hey KHill

Is it possible “ONLY_FULL_GROUP_BY” was enabled this week?

Do you encounter the same issue when including columns like 'status' and 'service_id' in the GROUP BY clause?

Thanks.

1 Like
3

Yuck - nasty MySQL allowing you to use a group by but ignore it if it's not correct.
From my more perfect world of Postgres and MS SQL, the question would be why it worked in the first place!
Just remove the group by in both bits of SQL. It's being ignored as it's not correct. Or, if you do want the group by, do it properly and include all the non-aggregated columns in the group by.

1 Like