CODEWITHSUNDEEP
sqlmysqlaggregate
ejel
ejel

Reputation: 4263

SQL query find max row from the aggregated averages

Supposed I have the following tables:

Sailor(sid, sname, age)  
Boat(bid, sid)

Each boat can have many sailors, and each individual sailor can serve on many boats. What I want to do is to find the boat with the highest average age of sailors.

I can find the average age of the sailor on each boat with this subquery: 

SELECT b.bid, AVG(s.age) AS avg_age FROM sailor s, boat b
WHERE b.sid = s.sid
GROUP BY b.bid

However, I am stuck on how to find the maximum row from this subquery further on.

P.S. I'm also looking for MySQL-compatible query, if that makes any difference.

Upvotes: 1

Views: 1840

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146429

In sql server it would be: 

  Select Top 1 Bid, Avg(age)
  From boat b Join sailor s 
     On s.sid = b.sid
  Group By Bid
  Order By Avg(Age) Desc

I'm not up on MySQL, but there's a Linmit keyword that "limits" the enumber of rows returned, If you use that instead of the Top 1, it should work, no? I'm not exactly sure of the syntax, but ...

  Select  Bid, Avg(age)
  From boat b Join sailor s 
     On s.sid = b.sid
  Group By Bid
  Order By Avg(Age) Desc
   Limit 1

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

SELECT t1.*
FROM
 (SELECT b.bid, AVG(s.age) AS avg_age FROM sailor s, boat b
  WHERE b.sid = s.sid
  GROUP BY b.bid) t1
LEFT OUTER JOIN
 (SELECT b.bid, AVG(s.age) AS avg_age FROM sailor s, boat b
  WHERE b.sid = s.sid
  GROUP BY b.bid) t2
 ON (t1.avg_age < t2.avg_age)
WHERE t2.avg_age IS NULL;

Upvotes: 2

dnagirl
dnagirl

Reputation: 20456

SELECT b.bid, AVG(s.age) AS avg_age 
FROM sailor s JOIN boat b USING (sid)
GROUP BY b.bid
ORDER BY AVG(s.age) DESC
LIMIT 1;

This will return one row, and because of the ordering it will contain the maximum average age.

Upvotes: 1

Related Questions