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 PostgreSQL

When window functions are evaluated
Summary

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 – the database gets the data from tables in FROM clause and if necessary performs the JOINs.
  2. WHERE – the data are filtered with conditions specified in WHERE clause.
  3. GROUP BY – the data are grouped by with conditions specified in WHERE clause.
  4. aggregate functions – the aggregate functions are applied to the groups created in the GROUP BY phase.
  5. HAVING – the groups are filtered with the given condition.
  6. window functions.
  7. SELECT – the database selects the given columns.
  8. DISTINCT – repeated values are removed.
  9. UNION/INTERSECT/EXCEPT – the database applies set operations.
  10. ORDER BY – the results are sorted.
  11. OFFSET – the first rows are skipped.
  12. LIMIT/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 their final_price higher than the average final_price.

Try to run this query. When you're done, click I'm done. Next exercise to continue.