Having both GROUP BY and ORDER BY in one query

Question

I've been told that I can't have GROUP BY and ORDER BY in one MySQL Query. Here is an abbreviated version of the query -

SELECT n.colorName, n.colorComp, n.colorID, SUM(n.gallons) AS TotalGallons
FROM netTran n, Store m, Product p
WHERE ((n.store = m.store) and m.state = "FL")
AND ((n.salesNbr = p.salesNbr) AND (p.intExt = "EXTERIOR" OR p.intExt = "INT/EXT"))
AND ((n.clrnt1 = "L1") AND (n.clrnt1 = "R3"))
GROUP BY n.colorComp, n.colorID
ORDER BY TotalGallons DESC;

I've been told that having the ORDER BY with the GROUP BY will give me different results and that the only way the ORDER BY would work is if the main query were nested in

SELECT * FROM
(query)
ORDER BY TotalGallons DESC;

Is that correct?

Solution 1

Use the query as

SELECT n.colorName, n.colorComp, n.colorID, SUM(n.gallons) AS     TotalGallons
FROM netTran n, Store m, Product p
WHERE ((n.store = m.store) and m.state = "FL")
AND ((n.salesNbr = p.salesNbr) AND (p.intExt = "EXTERIOR" OR p.intExt = "INT/EXT"))
AND ((n.clrnt1 = "L1") AND (n.clrnt1 = "R3"))
GROUP BY n.colorName, n.colorComp,n.colorID
ORDER BY TotalGallons DESC;

You can have grouo by and order by in a single query. But you need to provide all columns in case of you are aggregating a column

Solution 2

Group by will change the results.. Order by will just present data in order..

Having the ORDER BY with the GROUP BY won't give you different results

Comments

[Uueerdo]

No, that is not correct. ORDER BY works just fine following a GROUP BY; it will give you the same results in a different order; the only way you will get different results is if you also have a LIMIT. in fact if you wanted some thing "the three colors with the most total gallons", you would want that first query to also have LIMIT 3.

[Jim Macaulay]

Order by provides you the data in ascending or descending order. Group by aggregates the column. Hope you got an idea, else will explain you clearly

[BigRedEO]

That makes more sense. Thank you.

[Uueerdo]

My guess would be that ORDER BY overrides the implicit ordering of the GROUP BY, preventing GROUP BY from doing the ordering at all. However, they've warned that GROUP BY even doing the ordering is a deprecated behavior and not to assume it will be there in future versions.

[Gordon Linoff]

If you are going to rewrite the query, you should (1) motivate why and (2) use proper join syntax.