SQL Query - Raghuramakrishnan

Question

Write a SQL Query for : Find those ratings for which the average age of sailors is the minimum over all ratings.

Query 1: 

SELECT Temp.rating, Temp.avgage 

FROM (SELECT S.rating, AVG(S.age) AS avgage,

             FROM Sailors S

             GROUP BY S.rating) AS Temp

WHERE Temp.avgage = (SELECT MIN(Temp.avgage) FROM Temp)

Query 2 :

SELECT Temp.rating, MIN(Temp.avgage)

FROM (SELECT S.rating, AVG(S.age) AS avgage,

             FROM Sailors S

             GROUP BY S.rating) as Temp

GROUP BY Temp.rating

Are Query 1 and Query 2 equivalent ? Do comment.

Comments

both are different,

Q1 is correct for your query.

Q2 will select all the distinct ratings and corresponding avgs over same rating

---

is it because of the last line in Q2 ? else both would be equivalent ?

---

@just_bhavna

if you will remove last line in Q2(GROUP BY Temp.rating), then SQl engine will throw an error, because normal attributes and aggreate function can not be used simultaneously in SELECT clause without GROUP BY clause.

---

yes you are correct, missed that