Limiting results

These questions test your knowledge of the Over Command.


Read on to Learn about Over

Basic Over

Over is useful in limiting or tweaking the data returned from aggregate functions

Think of over as like a running total. For instance in our runners table, we have a query that selects name and average weight of all the runners seen so far when ordered by name. Here average_weight is recomputed on each 'step' of the SQL output

select
name, weight,
avg(weight) over (order by name)
from runners order by name
limit 3
nameweightavg_weight
andy5050
bob10075
cedric5063

Partition by

Partition by allows us to further subdivide the preceding Over Command

Here avg_weight is recomputed on every 'step' of the SQL like above BUT it is reset when the partition by field changes.

In this case we partition by country (think of it as grouping by country). There is a different aggregate function for each country

select
name, weight, country,
avg(weight) over (partition by country order by name)
from runners order by name
nameweightcountryavg_weight
andy50UK50
bob100UK75
cedric50France50
dave70Germany70
eric70France60

Preceding and Following

Preceding and Following allow us to perform aggregate functions on the rows just before and after the current row.

Here we will list the weight and the minimum weight of each runner and their neighbours just before and after them.

min(andy) = min(50, 100)
min(bob) = min(50, 100, 50)
min(cedric) = min(100, 50, 70)
min(dave) = min(50, 70, 70)
min(eric) = min(70, 70)
select
name, weight,
min(weight) over (order by name ROWS between 1 preceding and 1 following)
from runners order by name
nameweightmin
andy5050
bob10050
cedric5050
dave7050
eric7070