How to select only latest added records avoiding duplicates

I need to select from the table competition_rounds the latest added records which are linked to the competition_seasons table with the season_id column, eg:

competition_rounds

id |    name     | season_id
 1    Round A        20
 2    Round B        20
 3    Round A        21
 4    Round B        21

competition_seasons

id | name      | competition_id
 20  2017/2018      40
 21  2018/2019      40

I want return only:

  round_id  |   round_name   |  season_id | season_name
   3           Round A           21            2018/2019
   4           Round B           21            2018/2019

the problem’s that my query return all rounds available:

$sql = $this->db->prepare("SELECT max(r.id) AS round_id,
    r.name as round_name, r.season_id AS season_id, s.name AS season_name
    FROM competition_rounds r
    JOIN competition_seasons s ON r.season_id = s.id
    JOIN competition c ON s.competition_id = c.id
    WHERE c.id = :competition_id 
    GROUP BY r.id
    ORDER BY max(r.season_id) DESC");

$sql->bindParam("competition_id", 40);
$sql->execute();
$rounds = $sql->fetchAll();
return $response->withJson($rounds);

NB: The table competition simply contains a list of competition references.

Answer

If I understand correctly, you can use a subquery to return only the latest season from the seasons table:

SELECT r.id AS round_id,
       r.name as round_name, r.season_id AS season_id, s.name AS season_name
FROM competition_rounds r JOIN
     (SELECT s.*
      FROM competition_seasons s
      WHERE s.competition_id = :competition_id
      ORDER BY s.id DESC
      LIMIT 1
     ) s
     ON r.season_id = s.id ;

Your question only mentions two tables, although the query has three. This is only based on the question.