MySQL 5.1 to 5.7 change Order By no longer working

Question

I have a problem with MySQL creating a counter variable SOrder based on a set order defined by order by. It was working in MySQL 5.1 but currently not in 5.7. Original query in 5.1 had the subquery order by and was acceptable to the standards back then. Under 5.7 with only_full_group_by mode off, I added the order by to the outside but even when the order by within the subquery was commented out (or ignored according to SQL standards), it output SOrder as if it's random but not based on Column Carrier, PRIOR, ShipMethod, SingleModel.

SELECT 
    Carrier,
    PRIOR,
    ShipMethod,
    GroupOrder,
    @row_no:=IF(@prev_val = ANY_VALUE(t.SingleModel),
        @row_no,
        @row_no + 1) AS 'SOrder',
    SingleModel,
    COUNT(t.SingleModel) AS 'SingleModelTotQty',
    @prev_val:=ANY_VALUE(t.SingleModel)
FROM
    (SELECT 
        CASE
            WHEN ANY_VALUE(W.hdr_user_defined_field18) LIKE '%Fedex%' THEN 'FEDEX'
            ELSE 'UPS'
        END AS 'Carrier',
        CASE
            WHEN
                ANY_VALUE(W.hdr_user_defined_field20) IN ('16' , '26', '45', '88', '96')
                        OR ANY_VALUE(W.hdr_user_defined_field20) IN ('19' , '21', '22', '23', '24', '44', '76', '77', '78')
            THEN 'PRIOR'
            ELSE 'NORM'
        END AS 'PRIOR',
        CASE
            WHEN
                ANY_VALUE(W.hdr_user_defined_field20) IN ('88' , '96', '80', '97')
                    OR ANY_VALUE(W.hdr_user_defined_field20) IN ('76' , '77', '78', '79')
                    OR ANY_VALUE(W.`Ship-To Location`) != ''
            THEN 'S2S'
            ELSE 'S2H'
        END AS 'ShipMethod',
        CASE
            WHEN SUM(Quantity) = 1 THEN 'Single'
            WHEN
                SUM(Quantity) > 1 AND COUNT(`PO Number`) = 1
            THEN 'Multiple'
            WHEN COUNT(`PO Number`) > 1 THEN 'Mixed'
        END AS 'GroupOrder',
        CASE
            WHEN SUM(Quantity) = 1 THEN `Supplier Item Nbr`
            WHEN SUM(Quantity) > 1
                    AND COUNT(`PO Number`) = 1
            THEN ' '
            WHEN COUNT(`PO Number`) > 1 THEN ' '
        END AS 'SingleModel',
        `PO Number` AS 'PONUM',
        CASE
            WHEN SUM(Quantity) = 1 THEN ' '
            ELSE SUM(Quantity)
        END AS 'QtyPerMulPO'
    FROM
        dropship.Walmart_FullPOs W
    WHERE
        ProcessedDate = CURDATE() - 5
    GROUP BY `PO Number`
    **ORDER BY Carrier , PRIOR DESC , ShipMethod , GroupOrder DESC , SingleModel DESC**
    ) t,
    (SELECT @row_no:=0) x,
    (SELECT @prev_val:='') y
WHERE
    GroupOrder = 'Single'
GROUP BY Carrier , PRIOR , ShipMethod , SingleModel
ORDER BY t.Carrier , t.PRIOR DESC , t.ShipMethod , t.GroupOrder DESC , t.SingleModel DESC

Result

Answer 1

There is a problem with your sql_mode.

As of MySQL 5.7.x, the default sql mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default).

ONLY_FULL_GROUP_BY: Non-deterministic grouping queries will be rejected

For more details check the documentation of sql_mode

Method 1:

Check default value of sql_mode:

SELECT @@sql_mode

Remove ONLY_FULL_GROUP_BY from console by executing below query:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Method 2:

Access phpmyadmin for editing your sql_mode

• Login on phpmyadmin and open localhost
• Top on Variables present on the top in menu items and search out for sql mode
• Click on edit button to remove ONLY_FULL_GROUP_BY and save