Sunday, October 23, 2011

Finding the Maximum using Relational Algebra

We know that if you want to get the maximum value of a column in SQL, you can simply use the MAX function as shown below:
SELECT MAX(value) FROM T
You can also do it without the MAX function as follows:
SELECT T.* FROM T
MINUS
SELECT T.* FROM T, T as T2 WHERE T.value<T2.value
or:
SELECT T.* FROM T
LEFT JOIN T as T2 ON T.value<T2.value
WHERE T2.value IS NULL
Relational Algebra:
Using Relational Algebra (RA) syntax, this would be:
\project_{value}(T)
\diff
\project_{value} (
    \select_{value < value2}(
      \project_{value}(T)
      \cross
      \rename_{value2}(\project_{value}(T))
    )
)
where:
  • \cross is the relational cross-product operator
  • \diff is the relational diff operator
  • \project_{attr_list} is the relational projection operator
  • \rename_{new_attr_name_list} is the relational renaming operator
  • \select_{cond} is the relational selection operator

7 comments:

  1. Wow very helpful thanks!

    ReplyDelete
  2. Algebra is a major component of math that is used to unify mathematic concepts. Algebra is built on experiences with numbers and operations, along with geometry and data analysis. Some students think that algebra is like learning another language. This is true to a small extent, algebra is a simple language used to solve problems that can not be solved by numbers alone. It models real-world situations by using symbols, such as the letters x, y, and z to represent numbers.
    Rolle's Theorem

    ReplyDelete
  3. Thanks a lot! You saved me a lot of time! finding a max in RA is a tricky thing.

    ReplyDelete
  4. Thank you so much, Fahd!

    ReplyDelete
  5. Thanks a lot! It was very helpful.

    ReplyDelete
  6. Thanx a tonne . Your post was very helpful. Wishing you all the best. U seem to be THE BRAIN.

    ReplyDelete
  7. Thank you a LOT. The project immediately preceding the cross also needs renaming to "value1".

    Then it works - verified in the amazing Univ. of Insbruck RA calculator http://dbis-uibk.github.io/relax/calc.htm. Thank you!

    ReplyDelete