1

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???

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • 1
    Does this answer your question? [What is the execution sequence of Group By, Having and Where clause in SQL Server?](https://stackoverflow.com/questions/1130062/what-is-the-execution-sequence-of-group-by-having-and-where-clause-in-sql-serve) – philipxy Jun 13 '20 at 07:48
  • I added answer with example to explain the SQL Query Order of Execution, check this -> https://stackoverflow.com/a/62976524/6194097 – Kushan Gunasekera Jul 19 '20 at 05:23

8 Answers8

6

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
  • 854,459
  • 170
  • 1,222
  • 1,395
4

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.
  • 101,727
  • 34
  • 178
  • 212
1

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
  • 344,730
  • 71
  • 640
  • 635
1

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
  • 33,810
  • 26
  • 104
  • 151
1

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
  • 7,144
  • 20
  • 26
0

group by gets executed first and then the results of the group are ordered.

Evernoob
  • 5,551
  • 8
  • 37
  • 49
0

Let's assume we have SQL query:

SELECT   ...
  FROM     ...
  WHERE    ...
  GROUP BY ...
  HAVING   ...
  ORDER BY ...

the order in which sub-clauses of SQL query are executed is:

 1. FROM clause
 2. WHERE clause
 3. GROUP BY clause
 4. HAVING clause
 5. SELECT clause
 6. ORDER BY clause
Developer Marius Žilėnas
  • 5,813
  • 1
  • 41
  • 70
0

I will also suggest using a query analyzer for the specific database engine. The following is an example in Postgres, which explains that ORDER BY is executed first and after that the WHERE filter: 

EXPLAIN
SELECT * FROM table WHERE id=x AND date<='yyyy-mm-dd' ORDER BY date DESC;

So if I alter DESC to ASC the result set will contain different records!

Svetoslav
  • 563
  • 6
  • 14