Mysql – Getting max values from MySQL tables

MySQLsubquery

I have three tables:

  1. competitions (id, name, date)
  2. athletes (id,name)
  3. 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.

Best Answer

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` >=
        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` >= DATE('2012-10-08') + INTERVAL 0 SECOND
        AND `date` <  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...

Related Question