SQL using Min()

Question

I need to display the Youngest Athlete in my database. MIN(Athlete.Age) works fine, but I need the name as well obviously. When I add the Name to select I get about 5 results. How do I display just the MIN(Age) and her name?

select MIN(Athlete.Age), Athlete.Name
 from (((Country INNER JOIN Athlete ON Country.Country_Code = Athlete.Country_Code)
  INNER JOIN Athlete_event
   ON Athlete.Athlete_ID = Athlete_event.Athlete_ID2)
  INNER JOIN Event
   ON Event.Event_ID = Athlete_event.Event_ID2)
 Where Athlete.Athlete_ID = Event.Award_Gold
 GROUP BY Athlete.Name;

Steve Wellens · Accepted Answer · 2012-10-29 17:15:12Z

4

You may have 5 athletes with the same age. The query will not know which one you want.

If you have the birth-dates in the database, try using that.

[edit] Storing age is generally not a good idea because in one year, they will all be wrong.

[edit2] Note it is still possible for people to share the same birthday.

edited Oct 29, 2012 at 17:15

answered Oct 29, 2012 at 15:31

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2012-10-29 15:28:05Z

0

select * 
from
(
    select Athlete.Age, Athlete.Name
    from (((Country INNER JOIN Athlete ON Country.Country_Code = Athlete.Country_Code)
      INNER JOIN Athlete_event
       ON Athlete.Athlete_ID = Athlete_event.Athlete_ID2)
      INNER JOIN Event
       ON Event.Event_ID = Athlete_event.Event_ID2)
     Where Athlete.Athlete_ID = Event.Award_Gold  
    order by Age
) v
where rownum=1;

answered Oct 29, 2012 at 15:28

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

John Woo · Accepted Answer · 2012-10-29 15:28:50Z

0

add this line on your query

HAVING MIN(Athlete.Age) =  (SELECT MIN(AGE) from Athlete)

so your final query will look like

SELECT MIN(Athlete.Age),
        Athlete.NAME
FROM (((Country INNER JOIN Athlete
            ON Country.Country_Code = Athlete.Country_Code
        ) INNER JOIN Athlete_event
            ON Athlete.Athlete_ID = Athlete_event.Athlete_ID2
        ) INNER JOIN Event
            ON Event.Event_ID = Athlete_event.Event_ID2
        )
WHERE Athlete.Athlete_ID = Event.Award_Gold
GROUP BY Athlete.NAME
HAVING MIN(Athlete.Age) = ( SELECT MIN(AGE) FROM Athlete )

answered Oct 29, 2012 at 15:28

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

magodiez · Accepted Answer · 2012-10-29 15:46:01Z

0

if you use the GROUP BY in your query, you are searching the min age of each athlete.

You should try with a subquery. Adding it to your where clause. Something like: (Not tested)

select Athlete.Age, Athlete.Name
from Athlete
AND Athlete.Age = (select MIN(Athlete.Age)
from (((Country INNER JOIN Athlete ON Country.Country_Code = Athlete.Country_Code)
INNER JOIN Athlete_event
ON Athlete.Athlete_ID = Athlete_event.Athlete_ID2)
INNER JOIN Event
ON Event.Event_ID = Athlete_event.Event_ID2)
Where Athlete.Athlete_ID = Event.Award_Gold);

edited Oct 29, 2012 at 15:46

answered Oct 29, 2012 at 15:36

magodiez

7412 gold badges10 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL using Min()

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related