SQL Window Function

Functions

The window function can be any of the aggregation functions that we discussed above.

• Special window functions
  • ROW_NUMBER()→ # of the current row
  • RANK()→ Order position of the current row.

The OVER keyword specifies how to group together tuples when computing the window function.
Use PARTITION BY to specify group.

SELECT cid, sid, 
	ROW_NUMBER() OVER (PARTITION BY cid) 
FROM enrolled 
ORDER BY cid
 
SELECT *, 
	ROW_NUMBER() OVER (ORDER BY cid) 
FROM enrolled 
ORDER BY cid

Window frame

Links

• PostgreSQL: Documentation: 16: 3.5. Window Functions