Get last row in LEFT JOIN

What I am trying to do it with below code, getting all keywords with their positions via LEFT JOIN, it works fine but it shows the first position of each keyword, but I want to show the last position that recorded (by date).

PHP:

public function getKeyword(){
global $connection;
$array = Array();

$sql = "SELECT keyword.id, keyword.title, keyword.date, rank.position FROM keyword 
LEFT JOIN rank
ON rank.wordid = keyword.id
GROUP BY keyword.id
ORDER BY keyword.date DESC";
$result = $connection->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$array[] = $row;
}
}

return $array;
$connection->close();
}

How can I do this? Should I use sub query or what? is there any way to do this without a subquery?

SAMPLE DATA

What I want:

Get 17 instead of 13, I mean last record of position.

2 answers

  • answered 2018-06-24 19:36 Gordon Linoff

    Do not use group by for this! You want to filter, so use a where clause. In this case, using a correlated subquery works well:

    SELECT k.id, k.title, k.date, r.position
    FROM keyword k LEFT JOIN
         rank r
         ON r.wordid = k.id AND
            r.date = (SELECT MAX(r2.date)
                      FROM rank r2
                      WHERE r2.wordid = k.id
                     )
    ORDER BY k.date DESC
    

  • answered 2018-06-25 06:59 Prasad Wargad

    You can use below query

    SELECT keyword.id, keyword.title, keyword.date, rankNew.position FROM keyword LEFT JOIN (
    SELECT rank.wordid, rank.position FROM rank ORDER BY rank.id DESC LIMIT 0, 1) AS rankNew ON (rankNew.wordid = keyword.id);
    

    You can get more reference from Retrieving the last record in each group - MySQL