ansaurus

Question

SQL query find max row from the aggregated averages

Answer 1

+1 A:

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.

dnagirl 2009-09-29 01:00:40

Unfortunately LIMIT is not available on subqueries.

bobince 2009-09-29 01:03:42

MySQL does permit it (perhaps unintentionally) if you "double-subquery" it. E.g. `((SELECT ... LIMIT 1))`

Bill Karwin 2009-09-29 01:09:07

I read the "subquery" part of the question as being a red herring. It seemed to me that what he wants is "is to find the boat with the highest average age of sailors". So yes, if the query has to be a subquery, then LIMIT is a problem without the nesting @Bill Karwin suggests.

dnagirl 2009-09-29 01:14:25

I donlt understand, where's the subquery ??

Charles Bretana 2009-09-29 01:57:25

@Charles Bretana: That was my point. I believe that the questioner thought the answer required a subquery, not that a subquery was actually required.

dnagirl 2009-09-29 12:39:50

This query does work and I like the fact that it's a lot more concise than nested query, even though I was looking for a nested-query type of solution.

ejel 2009-09-29 17:03:37

Answer 2

+2 A:

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;

Bill Karwin 2009-09-29 01:01:12

+1 ugly but correct. Doing a separate query first with dnagirl's suggestion of LIMIT may be preferable to a subquery of this complexity if it is not looped. Note that there is no tie-breaking with self-null-joins; if two boats happen to have the same average age two rows will be returned.

bobince 2009-09-29 01:07:18

Yes, definitely ugly. I generally don't like to do `GROUP BY` in a subquery. Yuck!

Bill Karwin 2009-09-29 01:09:55

This is the solution I was exactly looking for. Even though it is not elegant and @dnagirl's solution might be preferable in real situation, I'd like to understand how SQL subquery works in this case.

ejel 2009-09-29 17:13:16

Answer 3

+1 A:

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

Charles Bretana 2009-09-29 01:47:09

ansaurus

tags:

views:

answers:

SQL query find max row from the aggregated averages

related questions