What is a window function?

Compute per row with OVER.

What is a window function?

A window function computes over a set of rows (a window). It returns results without collapsing rows.

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

This query gives each product a price rank.

name, price, RANK() OVER (ORDER BY price DESC) AS rk is selected from products. RANK() is a window function: it computes a value for each row over the window defined in OVER (…), without collapsing rows.

What is a window function? — details

A window function does not collapse like GROUP BY. Each row gets its own window result.

This lets you see each product's own price and the average price of all products on the same row, which GROUP BY cannot do.

Exercise What is a window function?

Find each product's price rank with RANK() OVER (ORDER BY price DESC).

Show example solution

SELECT name, price,
  RANK() OVER (ORDER BY price DESC) AS rk
FROM products;

Test yourself

What does a window function do to rows?
- Does not collapse them
- Collapses them
- Deletes them
- Sorts them
Show answer

Does not collapse them — A window function does not collapse rows.

Add OVER

RANK() ____ (ORDER BY price DESC)

Show answer

RANK() OVER (ORDER BY price DESC)

What is a window function: match the terms

window

window
OVER

over
RANK

rank
no collapse

preserves

Next lesson: ROW_NUMBER

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies