• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 25
  • Last Modified:

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
0
colinspurs
Asked:
colinspurs
  • 6
  • 4
1 Solution
 
Bill PrewCommented:
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
0
 
colinspursAuthor Commented:
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
0
 
colinspursAuthor Commented:
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

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Bill PrewCommented:
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
0
 
Bill PrewCommented:
Okay, I think I interpreted your original ask slightly differently.  So I don't think your query can get a lot different, although you shouldn't need to check for NULL on the INNER JOIN, and you shouldn't need to check for the 'W' and '2017' twice, so perhaps:

SELECT FR.fr_match_date,
       FR.fr_season_start,
       FP.fp_fixture_id,
       FP.fp_player_id,
       FP.fp_player_surname
FROM fixtures_players FP
INNER JOIN fixtures_results FR
ON FR.id = FP.fp_fixture_id
AND FR.fr_match_date = (SELECT MAX(FR2.fr_match_date)
                        FROM fixtures_results FR2
                        INNER JOIN fixtures_players FP2
                        ON FR2.id = FP2.fp_fixture_id
                        AND FR2.fr_team_code = 'W'
                        AND FR2.fr_season_start = 2017)
ORDER BY FR.fr_match_date DESC,
         FP.fp_player_id;

Open in new window


»bp
1
 
colinspursAuthor Commented:
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.
0
 
colinspursAuthor Commented:
Ignore my last contribution, we crossed!
0
 
colinspursAuthor Commented:
Thanks Bill, will check back later.  
Col
0
 
Bill PrewCommented:
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
1
 
colinspursAuthor Commented:
I think you have it here, except we do need the criteria in both inner and outer, otherwise it includes other team_codes.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now