Find youngest in each team in database

Question

I have a players table and a teams table and I am trying to find the youngest player in each team. I can find the youngest player:

SELECT lname, fname, dob, position
FROM players 
WHERE dob = (SELECT MAX(dob) FROM players);

When I try to include the teams table I am receiving the same data as above, which is the youngest player overall. I am very new to this so I am still trying to understand multiple row subqueries. If I order by team name, I will receive the same output. Do I need another SELECT statement for the teams table as well? How would I go about doing that if so? Changing operator from IN to ALL or ANY will also give me the same output.

SELECT lname, fname, dob, position, name
FROM players p JOIN teams t ON p.team_id = t.id
WHERE dob IN (SELECT MAX(dob) FROM players);

I am working in LiveSQL if that helps.

Answer 1

You don't even need the teams table to just get the player information. A correlated subquery is sufficient:

SELECT p.lname, p.fname, p.dob, p.position
FROM players p
WHERE p.dob = (SELECT MAX(p2.dob)
               FROM players p2
               WHERE p2.team_id = p.team_id
              );

You can introduce a JOIN to get the team name if you like:

SELECT p.lname, p.fname, p.dob, p.position, t.name
FROM players p JOIN
     teams t
     ON p.team_id = t.id
WHERE p.dob = (SELECT MAX(p2.dob)
               FROM players p2
               WHERE p2.team_id = p.team_id
              );

Answer 2

You can use NOT EXISTS as follows:

SELECT LNAME, FNAME, DOB, POSITION, NAME
  FROM PLAYERS   P
  JOIN TEAMS     T
ON P.TEAM_ID = T.ID
 WHERE NOT EXISTS (
    SELECT 1
      FROM PLAYERS P1
    WHERE P1.TEAM_ID = P.TEAM_ID
      AND P1.DOB < P.DOB
);