Search code examples
sqlmax

find maximum without aggregate function

I have the following schema for a table in SQL.

Owner (ownerid, name, phone, age)

I am asked to write a statement to find the oldest owner(s) in the database without using any aggregate function such as MAX. I'm also not allowed to use anything that is specific to any DBMS.

Thanks for your help.

Solution

  • You can use the EXISTS operator, which is standard on all SQL-compliant RDBMS:

    SELECT age
FROM Owner o1
WHERE NOT EXISTS (SELECT 1 FROM Owner o2 WHERE o2.age > o1.age)

    The idea behind this query is self-explanatory: the oldest owner is such an owner o1 that there are no other owner o2 such that o2 is older than o1.