CODEWITHSUNDEEP
sqlmax
Zoltan Magyar
Zoltan Magyar

Reputation: 85

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.

Upvotes: 2

Views: 10593

Answers (5)

Anuj Kumar
Anuj Kumar

Reputation: 1162

I am writing a few ways which I came up with after some thought.

Using TOP:

SELECT TOP 1 age
FROM Owner 
ORDER BY age DESC;

Using LIMIT:

SELECT age
FROM Owner
ORDER BY age DESC LIMIT 1

Using the ROW_NUMBER function:

SELECT x.age
FROM (SELECT age, 
               ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
      FROM Owner) x
 WHERE x.rank = 1;

Upvotes: 0

Timo Treichel
Timo Treichel

Reputation: 396

This Version don't need anything like "not in", "all" or "not exists".

select
    o1.age
from
    Owner o1
left join 
    Owner o2
on
    o2.age > o1.age
where
    o2.age is null;

Upvotes: 0

user330315
user330315

Reputation:

Another option is to use the ALL operator:

select o1.*
from "owner" o1
where age > all (select age 
                 from "owner" o2
                 where o2.ownerid <> o1.ownerid);

It essentially finds the owner where the age is greater than all age values except for the one we are looking at.

Upvotes: 0

Deepak Gupta
Deepak Gupta

Reputation: 197

You can also use NOT IN and JOIN operator which is standard on all SQL-complaint RDMS.

select age from owner where age not in (select o2.age from owner as o1 JOIN owner as o2 ON o1.age > o2.age);

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726639

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.

Upvotes: 3

Related Questions