Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you want just the ranking points awarded to an athlete in the latest competition, or a total of all an athlete's ranking points up to the competition date? Could you provide some example data, and examples of the output you'd like? –  Stuart Moore Oct 29 '12 at 9:59

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.