Getting max values from MySQL tables I have three tables:


competitions (id, name, date)
athletes (id,name)
results (place, id_athlete, id_competition, ranking_points)


where:

results.id_athlet=athlet.id
results.id_competition=competitions.id


I need a query to select the latest ranking_points of each athlete based on competitions.date.
The answer to this depends on what date range you pick

EXAMPLE : Ranking Points Since The Beginning of the Month for Competition 'Kids Only'

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Kids Only' AND `date` &gt;=
        DATE(NOW()) - INTERVAL (DAY(DATE(NOW()))-1) DAY + INTERVAL 0 SECOND;
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;


EXAMPLE : Teenage Rankings For the Week of 2012-10-08 - 2012-10-15

SELECT * FROM
(
    SELECT
        A.name Athlete,R.ranking_points Rank,C.`date` CompDate
    FROM
    (
        SELECT id,`date` FROM competitions
        WHERE name = 'Teenage'
        AND `date` &gt;= DATE('2012-10-08') + INTERVAL 0 SECOND
        AND `date` &lt;  DATE('2012-10-15') + INTERVAL 0 SECOND
    ) C
    INNER JOIN results R ON C.id = R.id_competition
    INNER JOIN athletes A ON R.id_athlete = A.id
) AA
ORDER BY Rank DESC;


I hope these give you a basis to start...