how to increase a search variable by 1 in sql query

Question

So the task is: Find the youngest sailor in each rating level

-

My tables:

Sailors(sid : integer, sname : string, rating : integer, age : real)

Reserves(sid : integer, bid : integer, day : date)

Boats(bid : integer, bname : string, color : string)

-

Is something like this even possible:

select min(age)

from sailors

where rating =(1++)

@lamak nope, as you probably understand, i am in the early stages of learning this.. — AdF, Mar 17 '15 at 16:50

score 0 · Answer 1 · answered Mar 16 '15 at 14:34

0

SELECT rating, MIN(age)
FROM Sailors
GROUP BY rating;

See the MySQL documentation on aggregate functions.

answered Mar 16 '15 at 14:34

Oli

919
8
16

it would help if you also use `rating` on the `SELECT` – Lamak Mar 16 '15 at 14:36
@Oli I feel a bit embarrased that it was this simple, but thanks. – AdF Mar 17 '15 at 16:59

avk · Answer 2 · 2015-03-16T14:42:30.907

0

select rating,
       min(age)
       from sailors
       where rating in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 )
       group by rating;

But if rating is a table it would be something like

select rating.id,
       min(age)
       from sailors inner join rating
       where rating.id = sailors.rating
       group by rating.id;

with this as a subquery, you could get the youngest sailor(s) like this:

select h.rating,
       sailor.sname,
       sailor.age
from sailor
inner join
     ( select rating,
       min(age) min_age
       from sailors inner join rating
       where rating.id = sailors.rating
       group by rating.id;
     ) h
where h.rating = sailor.rating
and   h.min_age = sailor.age;

edited Mar 16 '15 at 14:42

answered Mar 16 '15 at 14:36

avk

871
1
9
22

heh, would this work if two sailors had the same rating and age ? – AdF Mar 17 '15 at 17:32
Yes it would. Not sure if your data contains multiple minimum ages per rating, but it would work. – avk Mar 17 '15 at 17:56

score 0 · Answer 3 · answered Mar 16 '15 at 14:36

0

You should use GROUP BY.

SELECT MIN(age), rating
FROM   Sailors
GROUP BY rating

answered Mar 16 '15 at 14:36

Stanislovas Kalašnikovas

9,203
4
32
59

cheers, my first encounter with this community was better than i expected ;) – AdF Mar 17 '15 at 17:04

Srinath Mandava · Accepted Answer · 2015-03-17T21:10:24.523

0

select rating,sid,age
 from Sailors as S 
 where (age,rating) 
 in 
 (
     select min(age),rating
      from Sailors
    group by rating
 )

EDIT :

You could just do select rating,min(age) from sailors group by rating; to get the minimum age in the rating level but you won't get the details of the sailor having that minimum age..
Check this http://sqlfiddle.com/#!9/55276/1 where you can see that the sid is returned as 5 instead of 8 for rating 4...
where as this http://sqlfiddle.com/#!9/55276/3 returns the sid correctly

edited Mar 17 '15 at 21:10

answered Mar 16 '15 at 14:38

Srinath Mandava

3,384
2
24
37

What's the need for the `WHERE ... IN` clause here? You already have the minimum age and rating in the subquery. – Oli Mar 16 '15 at 14:40
Does MySQL support row and table constructors? – jarlh Mar 16 '15 at 14:40
@Oli In that case i doubt you would get only the min age but not the sailor details – Srinath Mandava Mar 16 '15 at 14:42
After testing i bit, i see the advantage in doing it like this. We haven't learned the where-in bit yet, but as far as i can understand its a simple test to see if age and rating is "in" the subquery. Thx anyway. – AdF Mar 17 '15 at 17:50
And is it not redundant to have this "where rating=S.rating" ? – AdF Mar 17 '15 at 17:59
@AdF where-in works the same way as you mentioned....Yeah and "where rating=S.rating" is redundant ...The advantage of doing the query this way is you also get the details of the sailor else in the accepted answer you get only the minimum age... – Srinath Mandava Mar 17 '15 at 21:02

how to increase a search variable by 1 in sql query

4 Answers4