CODEWITHSUNDEEP
sqlsqlplus
user1783150
user1783150

Reputation: 281

SQL using Min()

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;

Upvotes: 2

Views: 515

Answers (4)

Steve Wellens
Steve Wellens

Reputation: 20620

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.

Upvotes: 4

magodiez
magodiez

Reputation: 741

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);

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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 )

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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;

Upvotes: 0

Related Questions