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
LVL 3
Colin BrazierAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Colin BrazierAuthor 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
Colin BrazierAuthor 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
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Colin BrazierAuthor 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
Colin BrazierAuthor Commented:
Ignore my last contribution, we crossed!
0
Colin BrazierAuthor 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
Colin BrazierAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.