Question

Can any 1 tell me d answer for SQL query ??
table : Sailors(sailor id,boat id,sailorname,rating,age)
Query : find age of the youngest sailor for each rating level
2 - find age of youngest sailor wit age>18 , for each rating wit at least 2 sailors (of any age)...

Answer 1

wel, i can't give u d xact query but i'l give u an idea
1)select sailorid,min(age) from sailors group by rating;
2)select min(age) from sailors where sailos.age>18 group by rating having countsailors)>=2;

Answer 2

SELECT rating, MIN(age) FROM sailors GROUP BY rating HAVING COUNT(age) > 1

I believe will do the trick. Not 100% sure though.

Answer 3

Thanq....

Answer 4

Np. Click `good answer' next to my answer to give me a medal :)

Answer 5

@shadowfiend :can u plz xpalin wats d use off count(age)>1?

Answer 6

HAVING generally (as I recall) allows us to place a constraint on the groups before they are inserted into the set of results. In this case, COUNT(age) could just as easily be COUNT(*) -- the point is that it ensures that only groups of ratings that have more than one row in them are considered for the main SELECT conditions.

Answer 7

The second question can be solved with these two queries below:
a) select rating, min(age) from Sailors where age >= 18 and rating in (select rating from Sailors group by rating having count(sailor_id) >=2) group by rating

b) select rating, min(age) from Sailors s1 where age >= 18 and exists (select 1 from Sailors s2 where s1.rating = s2.rating group by rating having count(sailor_id) >=2) group by rating

Regards!