ORDER BY and DISTINCT cause the results to be displayed in an incorrect order

The following SQL

SELECT DISTINCT
    a.id,
    a.num
FROM
     a
LEFT JOIN   d ON a.id = d.stockId
WHERE
    a.cid = 156662
AND a.status = 3
AND d.wid IN (
    123,
)
ORDER BY
    a.num DESC
LIMIT 0,10

# # #

Num > 0; num > 0; num > 0;

The results show that DISTINCT has reordered the IDs and eventually LIMIT fetches the top 10 of the reordered data.

SQL > modify SQL > modify SQL >

1. 1. Change distinct to group by

2. ORDER BY A.NUM DESC, A.ID ORDER BY A.NUM DESC, A.ID

   Distinct IDs will be reordered, which may cause problems if the pagination results are displayed in order. Avoid using ORDER BY with DISTINCT and try to duplicate it with GROUP BY.