Aggregate Relational Algebra (Maximum)

by

You can very well express aggregate functions with only basic operators. It’s a pretty neat thing.

Suppose we have a table T, and we’d like to find the maximum of its “value” field. First, we should take the cartesian product of T with itself — or rather with a copy of itself, T2. Then we select the rows where T.value is smaller than T2.value: this nets us all the unwanted rows, whose value is less than the value of some other row. To get the maximal values, we should subtract these unwanted rows from the set of all rows. And that’s it. At least that’s the basic idea, we also need to use projections to get the dimensions right.

Unfortunately I have no idea how to insert Latex here, but using relational algebra notation, it’d be something like this:

π(T.a1...Tan, T.value)(T)
    -
π(T.a1...Tan, T.value)(
    σ(T.value<T2.value)( ρ(T, T2) x T )
)

where π is the projection operator, – is the set difference, σ is the selection operator and ρ is the rename operator.

SQLishly:

SELECT T.* FROM T
    MINUS
SELECT T.* FROM T, T as T2 WHERE T.value<T2.value

And more practically:

SELECT T.* FROM T LEFT JOIN T as T2 ON T.value<T2.value WHERE T2.value IS NULL

Of course, all this is mostly only of academic interest, i.e. that it shows that the relational algebra works.

More Related Contents:

  1. SQL Query to get column values that correspond with MAX value of another column?
  2. SQL select only rows with max value on a column [duplicate]
  3. Get the row(s) which have the max value in groups using groupby
  4. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  5. How do I get indices of N maximum values in a NumPy array?
  6. SELECTING with multiple WHERE conditions on same column
  7. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  8. must appear in the GROUP BY clause or be used in an aggregate function
  9. Finding max value in the second column of a nested list?
  10. What is the maximum length of an array in .NET on 64-bit Windows
  11. How many socket connections possible?
  12. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  13. Return index of greatest value in an array
  14. Find the item with maximum occurrences in a list [duplicate]
  15. Can min/max of moving window achieve in O(N)?
  16. Maximum size of a PHP session
  17. Use of min and max functions in C++
  18. How to define a custom aggregation function to sum a column of Vectors?
  19. What is a self join for? (in english)
  20. Group DateTime into 5,15,30 and 60 minute intervals
  21. Why can’t you mix Aggregate values and Non-Aggregate values in a single SELECT?
  22. LINQ aggregate and group by periods of time
  23. How can I find the maximum value and its index in array in MATLAB?
  24. Custom aggregate function (concat) in SQL Server
  25. How can I take pairwise parallel maximum or minimum between two vectors?
  26. Finding the maximum value for each row among 3 columns in R
  27. Get most recent date from an array of dates in “Y-m-d H:i:s” format
  28. How do I find the maximum (larger, greater) of 2 numbers?
  29. Find the max of 3 numbers in Java with different data types
  30. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

Leave a Comment