MySql Select statement distinct latest date value based on Primary ID of Keyword

Question

Basically I have a list of SEO keywords in one table and their Ranking including Date entered in another table.

keywords

[keywordID(PKID)] [keyword]
----------------------------
1                 SEO
2                 Vancouver Homes

keywordRanking

[rankingID(PKID)] [keywordID(FKID)] [ranking] [rankingDate(timestamp)]
----------------------------------------------------------------------
1                 1                 3         Jan 01, 2010
2                 1                 5         Jan 02, 2010
3                 1                 8         Jan 03,2010
4                 2                 4         Jan 01, 2010

Here are the results I want. I want the latest ranking of a keyword based on its latest ranking Date without duplicates.

For the data above, it should return:

                  [keywordID] [rankingID] [ranking] [rankingDate] 
                  -----------------------------------------------
 SEO              1           3           8         Jan 03, 2010
 Vancouver Homes  4           2           4         Jan 01, 2010

What would the statement be to select the latest ranking for a keyword? Right now I have it returning duplicate rows for the keywordID.

Please ask if more information is required here is my current sql statement.

SELECT * 
  from keywords,
       keywordRankings 
 WHERE keywords.keywordID = keywordRankings.keywordID;

My current solution which works for me is...

SELECT s1.keywordID, keyword, rankingDate, ranking
FROM keywords s1, keywordRankings
WHERE keywordRankingRecord = (
SELECT MAX( s2.keywordRankingRecord )
FROM keywordRankings s2
WHERE s1.keywordID = s2.keywordID )     

Answer 1

The fundamental idea behind finding the maximum among a group is explained here.

SELECT k.keyword,
       r1.*
FROM   keyword AS k
       LEFT JOIN keywordRanking AS r1
         ON k.keywordid = r1.keywordid
       LEFT JOIN keywordRanking AS r2
         ON k.keywordid = r2.keywordid
            AND r1.rankingdate < r2.rankingdate
WHERE  r2.ranking IS NULL  

yields

+-----------------+-----------+-----------+---------+---------------------+
| keyword         | keywordID | rankingID | ranking | rankingDate         |
+-----------------+-----------+-----------+---------+---------------------+
| SEO             |         1 |         3 |       8 | 2010-01-03 00:00:00 | 
| Vancouver Homes |         2 |         4 |       4 | 2010-01-01 00:00:00 | 
+-----------------+-----------+-----------+---------+---------------------+