What will be the sequence of execution followed by SQL if a query has both group by and order by clause. Does it depend on their position in the query???
A:
group by gets executed first and then the results of the group are ordered.
Evernoob
2009-09-21 14:27:47
+4
A:
ORDER BY always executes on the results of the grouping performed by GROUP BY, i.e., always "after". In standard SQL, you must have ORDER BY lexically after GROUP BY, if both are present, to kind of "remind you" of the fact.
Alex Martelli
2009-09-21 14:27:48
+1
A:
It depends on many things including the RDMS you are using. The best way to find out what is going on is to use a query tool that allows you to see the query execution plan.
Andrew Hare
2009-09-21 14:28:31
+1
A:
Order by generally happens last.
If you're using SQL Server, fire up query analyzer and execution plan will give you a nice graphical representation of your query.
Chris Ballance
2009-09-21 14:30:14
+2
A:
in order:
FROM & JOINs determine & filter rows
WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups
KM
2009-09-21 15:35:22
@KM: any links?
p.campbell
2009-09-21 15:42:50
+1
A:
The sequence of execution is not mandated by any SQL statement. What is mandated is that the result match the result that would be obtained by a "canonical" evaluation. In the canonical evaluation, the ORDER BY is applied last (even after the SELECT list expressions are evaluated), but that doesn't mean sorting is postponed to that point in the actual execution of a query on a real system.
Steve Kass
2009-09-21 15:46:07