Get last row in LEFT JOIN

Question

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

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

How can I do this? Should I use subquery 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.

`SELECT keyword.id, keyword.title, keyword.date, rank.position ... GROUP BY keyword.id` is a invalid query which will case invalid results.. Priove example data with expected ouput place it on http://sqlfiddle.com/ or https://www.db-fiddle.com/.. And also give us the MySQL version with `SELECT VERSION()` — Raymond Nijland, Jun 24 '18 at 19:23
This explains why http://www.riptutorial.com/mysql/example/26486/using-and-misusing-group-by — Raymond Nijland, Jun 24 '18 at 19:25
@tourtravel . . . Sample data and desired results would help. — Gordon Linoff, Jun 24 '18 at 19:36

Gordon Linoff · Accepted Answer · 2018-06-24T19:52:04.807

2

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

edited Jun 24 '18 at 19:52

answered Jun 24 '18 at 19:36

Gordon Linoff

1,242,037
58
646
786

I got error `Trying to get property of non-object` with your code – Jack The Baker Jun 24 '18 at 19:40
@tourtravel . . . Your question suggests that the rankings have a date field "want to show the last position that recorded (by date)". You may need to use the correct column name. – Gordon Linoff Jun 24 '18 at 19:43
1

"Trying to get property of non-object" is a PHP error not a MySQL error @tourtravel – Raymond Nijland Jun 24 '18 at 19:46
@RaymondNijland Did you see my code in question? I copied this answer to `$sql = "..."` so what? I run my code and it return a php error with this code. – Jack The Baker Jun 24 '18 at 19:47
@GordonLinoff both has `date` field.. I don't understand you. – Jack The Baker Jun 24 '18 at 19:51
@tourtravel . . . The `rank.position` in the `SELECT` would have caused a query error. You should be checking for errors whenever you run queries. In any case, that's now fixed. – Gordon Linoff Jun 24 '18 at 19:52

score 0 · Answer 2 · answered Jun 25 '18 at 06:59

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

Get last row in LEFT JOIN

2 Answers2