Quota queries

Building on the basic relational operations, we have the ability to perform quota queries. An example of a quota query is "find the three heaviest parts".

Quota

The quota operator takes a relational expression, an ordered set of attributes and a quota count. Each attribute name in the ordered set can be prefixed with - to use a descending order, otherwise an ascending order is used.

Again, given the example parts relvar:

P

PNO
PNAME
COLOR
WEIGHT
CITY

P1
Nut
Red
12
London
P2
Bolt
Green
17
Paris
P3
Screw
Blue
17
Rome
P4
Screw
Red
14
London
P5
Cam
Blue
12
Paris
P6
Cog
Red
19
London

We can get the three heaviest parts using the quota operator.

For example:

quota(P, {-WEIGHT}, 3)

PNO
PNAME
COLOR
WEIGHT
CITY

P2
Bolt
Green
17
Paris
P3
Screw
Blue
17
Rome
P6
Cog
Red
19
London

Note:

• If there are fewer parts than the quota count, the operation can return under the quota count.
• If there are more than the quota count with the same values for the ordered set of attributes, then the operation can return over the quota count.

Another example to find the part(s) with the smallest weight:

quota(P, {WEIGHT}, 1)

PNO
PNAME
COLOR
WEIGHT
CITY

P1
Nut
Red
12
London
P5
Cam
Blue
12
Paris

Notice we get two tuples because they share the smallest weight. We can break the tie here if we want by adding another attribute to the ordering, e.g. -PNO to mean: if there's a tie choose the part with the largest PNO:

quota(P, {WEIGHT, -PNO}, 1)

PNO
PNAME
COLOR
WEIGHT
CITY

P5
Cam
Blue
12
Paris

Another example, to "get all suppliers whose status is one of the two smallest":

quota(S{STATUS}, {STATUS}, 2) & S

STATUS
SNO
SNAME
CITY

10
S2
Jones
Paris
20
S1
Smith
London
20
S4
Clark
London

Shorthand

Note: the quota operator is just a shorthand for something like this:

P{*, heavier:=count(P{*, WT:=WEIGHT}[WT > WEIGHT])}[heavier < 3]~{heavier}