Order BY Does not give proper results in left join scenario

Question

This is the original query I'm using and I'm getting the sales amount sorted by the business day (1,5,..31).

        SELECT somelogic AS TOTAL,IT.BUSINESS_DAY,IT.BUSINESS_MONTH, IT.BUSINESS_YEAR

        FROM (
            SELECT SUM(IFNULL(CAST(FSTM.SALE AS DECIMAL(16,4)), 0)) AS ITEM_SALES, FSTM.BUSINESS_YEAR, FSTM.BUSINESS_MONTH, FSTM.BUSINESS_DAY
            FROM AGG_FACT_SALE_SALE_TYPE_DAY FSTM WHERE
            SALE_TYPE IN ('SALE', 'VOID', 'REOPENED') AND FSTM.MERCHANT_KEY = ${Mkey}
            AND FSTM.YEAR = ${Year} AND FSTM.MONTH = ${Month}
            GROUP BY FSTM.BUSINESS_DAY ORDER BY BUSINESS_DAY
            ) AS IT

        LEFT JOIN (
            sub query
            ) AS LD ON LD.BUSINESS_DAY = IT.BUSINESS_DAY

        LEFT JOIN (
            sub query
        ) AS DC ON DC.BUSINESS_DAY = IT.BUSINESS_DAY
        LEFT JOIN (
            subquery
        ) AS R ON R.BUSINESS_DAY = IT.BUSINESS_DAY

So in the 'from' I'm ordering the results according to the business day, then use left join to get the rest of the data.. But in the final result results are not sorted by the business day. Its almost random.

But when I add the ORDER BY To the whole query Im getting the sorted results. Modified query is given below.

        SELECT somelogic AS TOTAL,IT.BUSINESS_DAY,IT.BUSINESS_MONTH, IT.BUSINESS_YEAR

        FROM ((
            SELECT SUM(IFNULL(CAST(FSTM.SALE AS DECIMAL(16,4)), 0)) AS ITEM_SALES, FSTM.BUSINESS_YEAR, FSTM.BUSINESS_MONTH, FSTM.BUSINESS_DAY
            FROM AGG_FACT_SALE_SALE_TYPE_DAY FSTM WHERE
            SALE_TYPE IN ('SALE', 'VOID', 'REOPENED') AND FSTM.MERCHANT_KEY = ${Mkey}
            AND FSTM.YEAR = ${Year} AND FSTM.MONTH = ${Month}
            GROUP BY FSTM.BUSINESS_DAY ORDER BY BUSINESS_DAY ASC
            ) AS IT

        LEFT JOIN (
            sub query
            ) AS LD ON LD.BUSINESS_DAY = IT.BUSINESS_DAY

        LEFT JOIN (
            sub query
        ) AS DC ON DC.BUSINESS_DAY = IT.BUSINESS_DAY
        LEFT JOIN (
            subquery
        ) AS R ON R.BUSINESS_DAY = IT.BUSINESS_DAY
        ) ORDER BY IT.BUSINESS_DAY ASC

So can some one shed some light why this happens ?

If you don't order the whole resultset, it won't be ordered. It CAN be (by coincidence) ordered and give the impression that the order of the subquery matters for the whole query, but it doesn't (just like e.g. `select * from table` is often ordered by the primary key, but that is just randomly that way, and not necessarily always the case). If you want an order for the whole query, you have to specify one. See for example here: [mysql - order by inside subquery](http://stackoverflow.com/questions/26372511/mysql-order-by-inside-subquery) — Solarflare, Jan 18 '17 at 09:56
Possible duplicate of [mysql - order by inside subquery](http://stackoverflow.com/questions/26372511/mysql-order-by-inside-subquery) — philipxy, Jan 28 '17 at 01:51
ORDER BY has no effect when it's not on the outside of a query statement. Tables have no row order. An outermost query doesn't actually return a table, it returns a sequence of rows. — philipxy, Jan 28 '17 at 01:51

Order BY Does not give proper results in left join scenario

0 Answers0