Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Evaluation Order and Common Problems
Window functions and GROUP BY
Summary and Review

Instruction

Great, now we can get down to work. Back in Part 1, we said that you can't use window functions in the WHERE clause. Why is that so? Because all query elements are processed in a very strict order:

  1. FROM – SQL Server gets data from tables in the FROM clause and, if necessary, performs the JOINs (INNER, LEFT, RIGHT and FULL)
  2. WHERE – The data is filtered by the conditions specified in the WHERE clause
  3. GROUP BY – The data is grouped by the conditions specified in the WHERE clause,
  4. Aggregate Functions are applied to the groups created in the GROUP BY phase
  5. HAVING – The aggregated groups are filtered by the condition given in the HAVING clause
  6. Window Functions
  7. SELECT – SQL Server selects the given columns
  8. DISTINCT – Repeated values are removed
  9. UNION/INTERSECT/EXCEPT – SQL Server applies set operations
  10. ORDER BY – the results are sorted
  11. OFFSET – One or more rows are skipped
  12. FETCH/TOP – only the first rows are selected

Practically, this order means that you can't put window functions anywhere in the FROM, WHERE, GROUP BY or HAVING clauses. This is because at the time of calculating these elements, window functions are not yet calculated – and it's impossible to use something which is not already available.

Exercise

Find out for yourself that window functions don't work in the WHERE clause. Look at the template: we would like to show some information for those auctions which have a higher FinalPrice than the average FinalPrice.

Try to run this query.

When you're done, click I'm done. Next exercise to continue.