Finding max without aggregate operation in relational algebra

Question

How can I find the maximum without using aggregate operation in relational algebra?

The schema of Database is as follows

• Item(IName, Brand)
• Shop(SName, City, Address)
• Sells(IName, SName, Price)

How can I find the item name and snop name which is sold in maximum price without using aggregate function in relational algebra? I know to solve this with using aggregate functions but not sure without using it.

Answer 1

In SQL, but not in relational algebra

You can sort by Price descendingly and limit the results to 1.

Example in MySQL

SELECT IName, SName, Price
FROM Sells
ORDER BY Price DESC
LIMIT 0, 1;

The highest Priced item will be the first record in the result and due the to limit you ignore anything else.

Alternatively, you can find the r1 record which has no r2 record with higher price:

SELECT r1.IName, r1.SName, r1.Price
FROM Sells r1
LEFT JOIN Sells r2
ON r1.Price < r2.Price
WHERE r2.Price IS NULL
LIMIT 0, 1;

The query above selects the items for which we do not find a higher priced pair. LEFT JOIN allows the right-hand-side to be nonexistent, so any r1 record will have at least a pair, but those r1 records that have the greatest price will have all nulls in r2. Equating for a column with null ensures that we filter out all r1 records that had a higher priced pair. We still have a LIMIT clause, because in the case of a tie, i.e. more records share the same maximum Price then we break the tie by selecting the first item that matched the criteria.

By default, the first approach I have shown should be preferred, but, if it is not an option for some reason, then you can go by the second approach.

EDIT

Relational algebra

@philipxy pointed out in the comment section that relational algebra has no null or sorting or limit. As a result, in relational algebra one would need to write an expression that searches among the records that which has a price for which does not exist another record whose price is higher.