asked on

Limit results to the most recent instance of the parent table

Hi experts, I've been out of practice with SQL lately. That's my excuse and I'm sticking to it.

This query tells me the players selected for fixtures where players have been selected (for some fixtures, no players are selected).

I need to get the info from just the latest such fixture, i.e. the most recent where players have been selected. How would I best do this?

fixtures_results
id
fr_team_code
fr_match_date
...etc

fixtures_players
fp_fixture_id
fp_player_id
fp_player_surname
...etc

SELECT FR.fr_match_date, FP.fp_player_id
FROM fixtures_results FR INNER JOIN fixtures_players FP
ON FR.id = FP.fp_fixture_id
WHERE FP.fp_fixture_id IS NOT NULL
AND FR.fr_team_code = 'W'
AND FR.fr_season_start = 2017
ORDER BY FR.fr_match_date DESC, FP.fp_player_id

Bill Prew

Can you provide a sample of the data, not sure I completely understand what the tables will contain, and how they will be connected.

»bp

Colin Brazier

ASKER

Fair enough. Here are two data samples.

I just need info from the most recent fixture where there are players selected, in this instance, fixture 6717 and its players only.
fixtures.xlsx
players.xlsx

Colin Brazier

ASKER

This gives the results I want...can I simplify it?

SELECT FR.fr_match_date, FP.fp_fixture_id, FP.fp_player_id
FROM fixtures_results FR INNER JOIN fixtures_players FP 
    ON FR.id = FP.fp_fixture_id
WHERE FR.fr_team_code = 'W'
  AND FR.fr_season_start = 2017
  AND FR.fr_match_date = (
                       SELECT max(fr_match_date)
                       FROM fixtures_results FR INNER JOIN fixtures_players FP 
                       ON FR.id = FP.fp_fixture_id
                       WHERE FP.fp_fixture_id IS NOT NULL
                       AND FR.fr_team_code = 'W'
                       AND FR.fr_season_start = 2017
                       )

Open in new window

Bill Prew

I think does what you are looking for.

SELECT
    MAX(FR.fr_match_date) AS fr_match_date,
    FR.fr_season_start,
    FP.fp_player_id,
    FP.fp_player_surname
FROM fixtures_results FR
INNER JOIN fixtures_players FP
    ON FR.id = FP.fp_fixture_id
WHERE FP.fp_fixture_id IS NOT NULL
AND FR.fr_team_code = 'W'
AND FR.fr_season_start = 2017
GROUP BY FR.fr_season_start,
         FP.fp_player_id,
         FP.fp_player_surname
ORDER BY fr_match_date,
         FR.fr_season_start,
         FP.fp_player_id,
         FP.fp_player_surname;

Open in new window

»bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Colin Brazier

ASKER

I have amended it slightly to remove surname and include th fixture ID, and to sort descending.

SELECT
    MAX(FR.fr_match_date) AS fr_match_date,
    FR.id,
    FR.fr_season_start,
    FP.fp_player_id
FROM fixtures_results FR
INNER JOIN fixtures_players FP
    ON FR.id = FP.fp_fixture_id
WHERE FP.fp_fixture_id IS NOT NULL
AND FR.fr_team_code = 'W'
AND FR.fr_season_start = 2017
GROUP BY FR.id,
         FR.fr_season_start,
         FP.fp_player_id
ORDER BY fr_match_date DESC,
         FR.id,
         FR.fr_season_start,
         FP.fp_player_id;

Open in new window

It brings back all games that meet the criteria, not just the one on the latest date.

Colin Brazier

ASKER

Ignore my last contribution, we crossed!

Colin Brazier

ASKER

Thanks Bill, will check back later.
Col

Bill Prew

Not sure it simplifies much, but I find CTE's can sometimes make things a bit more modular, so here's an approach with that technique.

WITH FR_CTE (fr_match_date, id) AS 
(
    SELECT MAX(fr_match_date),
           MAX(id)
      FROM fixtures_results
INNER JOIN fixtures_players
        ON id = fp_fixture_id
     WHERE fr_team_code = 'W'
       AND fr_season_start = 2017
)
    SELECT fr_match_date,
           fp_fixture_id,
           fp_player_id
      FROM FR_CTE
INNER JOIN fixtures_players
        ON id = fp_fixture_id
  ORDER BY fr_match_date DESC,
           fp_player_id;

Open in new window

»bp

Colin Brazier

ASKER

I think you have it here, except we do need the criteria in both inner and outer, otherwise it includes other team_codes.

Thanks.