I have to build the following query but don't have idea in the select max() part.

Having these 2 tables:

players

```
-------------
| id | name |
-------------
| 1  | AAAA |
-------------
| 2  | BBBB |
-------------

```

scores

```
----------------------------------------
| player_id | score | created          |
----------------------------------------
| 1         | 1080  | 2015/12/28 15:38 |
----------------------------------------
| 1         | 1199  | 2015/12/28 16:00 |
---------------------------------------|
| 2         | 2400  | 2015/12/29 10:30 |
----------------------------------------
| 2         | 2420  | 2015/12/29 10:41 |
----------------------------------------
| 2         | 2031  | 2015/12/29 11:12 |
----------------------------------------

```

Result I would like to have:

```
----------------------------
| id | name | latest_score |
----------------------------
| 1  | AAAA | 1199         |
----------------------------
| 2  | BBBB | 2031         |
----------------------------

```

SQL Query #1 - This query gives the above result I want

```
SELECT
  players.id AS id,
  players.name AS name,
  scores.player_id,
  scores.score AS latest_score,
  scores.created
FROM
  players
JOIN scores ON
  players.id = scores.player_id
WHERE
  scores.created IN(
  SELECT MAX(scores.created)
  FROM
    scores
  WHERE
    scores.player_id= player.id
)

```

SQL Query #2 - By @tim-biegeleisen, works too, so or you may help me to build this query?

```
SELECT p.id, p.name, s1.score AS latest_score
FROM players p INNER JOIN scores s1
ON p.id = s1.player_id
INNER JOIN
(
    SELECT s.player_id, MAX(s.created) AS latest
    FROM scores s
    GROUP BY s.player_id
) s2
ON s1.player_id = s2.player_id AND s1.created = s2.latest

```

Currently trying to build something like:

```
    $query = $this->Players->find()
            ->select([
                "Players.id",
                "Players.name",
                "Scores.scores",
                "Scores.created"
            ])
            ->join([
                "Scores" => [
                    "table" => "scores",
                    "type" => "left",
                    "conditions" => "Players.id = Scores.player_id"
                ]
            ])
            ->where([
                "Scores.created" =>
                $this->Players->Scores->find()
                ->select(["MAX(Scores.created)"])
                ->where(["Scores.player_id = Players.id"])
            ]);

```

**Question** is, this builder doesn't work: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MAX(`Scores\_\_created`) FROM scores Scores WHERE Scores.player\_id = Players.i' at line 1

I don't want to just build a join and then order the `Scores.created` desc, and grab the `[0]` to get the first row. You know how to build this query? Please help.

Thank you!