OrderBy Dilema

[danyDude]

Hy, I have something like this:

SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);

The dilema is that

ORDER BY Defects

does not work and

ORDER BY SUM(QualityData.NrOfDefects);//causes a second SUM??

Is there a better way? Thanks

[loyal ginger]

SELECT Code, Defects from
(SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code)
ORDER BY Defects;

[danyDude]

thanks

[i j russell]

Your initial query is fine; it will not require a second set of calculations for the sort. To verify this, run the query in Management Studio and look at the Actual Execution Plan.

[loyal ginger]

OK. I did not have access to much resources when I answered your question. I assumed that you had problems with your original queries. However, here I have access to my environment, and I tested your query

SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);

It works with no problem. It works with no problem if I use

ORDER BY Defects

So just ignore my "solution". There must be something else in your problem. My test environment is MySQL version 5.

[danyDude]

It's all fixed now. I now know for a fact(tested) that even the

ORDER BY SUM(QualityData.NrOfDefects);

does not cause a recalculation.