How can I find MAX with relational algebra?

Question

Working with databases, how can I find MAX using relational algebra?

Answer 1

Assuming you have a relation, A, with a single attribute, 'a' (reducing a more complex relation to this is a simple task in relational algebra, I'm sure you got this far), so now you want to find the maximum value in A.

One way to do it is to find the cross product of A with itself, be sure to rename 'a' so your new relation has attributes with distinct names. for example:

(rename 'a' as 'a1') X (rename 'a' as 'a2')

now select 'a1' < 'a2', the resulting relation will have all values except the maximum. To get the max simply find the difference between your original relation:

(A x A) - (select 'a1' < 'a2') ((rename 'a' as 'a1')(A) x (rename 'a' as 'a2')(A)) 

Then use the project operator to reduce down to a single column as Tobi Lehman suggests in the comment below.

Writing this in relational algebra notation would be (if I remember correctly). Note the final rename (i.e. ρ) is just to end up with an attribute that has the same name as in the original relation:

ρ_a/a1(π_a1((A x A) - σ_{a1 < a2} (ρ_a1/a(A) x ρ_a2/a(A))))