Calculate Recent Form Football standings ( for example Last 5 games standings )

  • g.debrucker

    SSC Veteran

    Points: 292

    Hi all,

    I'm very new to SQL and MS-Access. But My hobby is driving me to databsetools 🙂 I collect every soccer game around europe but my excelsheet can't handle my formulas anymore. So that's why I want to use an access db with some queries.

    This is my table:

    Name: GamesTable

    Columns:

    ID

    Date

    Team

    GoalsScored

    GoalsAgainst

    But now I want to retrieve a list of every team's last 5 matches. So I assume something with distinct date limit 5 ???

    Can someone help my out with this one! A lifetime of gratitude will follow 😀

  • J Livingston SQL

    SSC Guru

    Points: 51272

    g.debrucker (11/16/2015)


    Hi all,

    I'm very new to SQL and MS-Access. But My hobby is driving me to databsetools 🙂 I collect every soccer game around europe but my excelsheet can't handle my formulas anymore. So that's why I want to use an access db with some queries.

    This is my table:

    Name: GamesTable

    Columns:

    ID

    Date

    Team

    GoalsScored

    GoalsAgainst

    But now I want to retrieve a list of every team's last 5 matches. So I assume something with distinct date limit 5 ???

    Can someone help my out with this one! A lifetime of gratitude will follow 😀

    you have posted into a sql7/2000 forum.......is this a mistake?

    if you are going to use MS Access, then I would strongly advise you to seek help in an Access forum (such as

    http://www.utteraccess.com/) the reason being that this forum is MS SQL orientated.

    of course...if you want SQL advice then we will be pleased to help.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • g.debrucker

    SSC Veteran

    Points: 292

    Yes the sql code to get this out of my database would be much apreciated.

    Probably I will use MySQL in the future so If you could help me with this one , would be awesome!

  • J Livingston SQL

    SSC Guru

    Points: 51272

    g.debrucker (11/16/2015)


    Yes the sql code to get this out of my database would be much apreciated.

    Probably I will use MySQL in the future so If you could help me with this one , would be awesome!

    what database are you currently using...MS SQL/ MS Access / MYSQL.....???

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • g.debrucker

    SSC Veteran

    Points: 292

    for the moment MS Access, But have the possibility to switch over to MySQL.

  • Luis Cazares

    SSC Guru

    Points: 183524

    Just to be clear.

    MS Access, MS SQL Server, MySQL, PostgreSQL and others are different products to handle databases. They share similarities, but queries are written different for each one of them.

    This forum is focused on MS SQL Server, and while you might get basic help for the others, the best help you'll get here will only be for MS SQL Server.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • g.debrucker

    SSC Veteran

    Points: 292

    Than ms sql it is .

    Have no problem of changing my data to other db.

    Just need this sql query 🙂 Am searching for it quit a while

  • Luis Cazares

    SSC Guru

    Points: 183524

    There's a lot to be done. As you don't have nothing installed yet, I'll assume that you'll get the latest version available (2014). I strongly suggest that you read the stairway on database design[/url] which will make your data more consistent.

    Now, here's an example on how you could generate a query as you described. Note that I include sample data in a way that can be easily copied, past and run to have it available. You're supposed to do that for us in future questions posted.

    --Generate some sample data

    CREATE TABLE Teams(

    TeamID int IDENTITY PRIMARY KEY,

    TeamName varchar(30)

    );

    --

    CREATE TABLE Games(

    GameID int IDENTITY PRIMARY KEY,

    GameDate date,

    TeamID int,

    GoalsScored int,

    GoalsAgainst int

    CONSTRAINT FK_Games_Teams FOREIGN KEY (TeamID) REFERENCES Teams(TeamID)

    );

    INSERT INTO Teams

    VALUES('Liverpool'),

    ('Chelsea'),

    ('Real Madrid'),

    ('Barcelona');

    INSERT INTO Games

    VALUES('20151010', 1, 1, 2), --This game shouldn't appear

    ('20151015', 1, 7, 2),

    ('20151020', 1, 3, 1),

    ('20151025', 1, 2, 2),

    ('20151105', 1, 3, 4),

    ('20151115', 1, 3, 2),

    ('20151115', 2, 2, 3),

    ('20151115', 3, 3, 3),

    ('20151115', 4, 3, 3);

    WITH cteGames AS(

    SELECT g.GameID,

    g.GameDate,

    t.TeamName,

    g.GoalsScored,

    g.GoalsAgainst,

    --Generate a number to "count" games per team from most recent to oldest

    ROW_NUMBER() OVER(PARTITION BY t.TeamID ORDER BY g.GameDate DESC) RowNum

    FROM Games g

    JOIN Teams t ON g.TeamID = t.TeamID

    )

    SELECT GameID,

    GameDate,

    TeamName,

    GoalsScored,

    GoalsAgainst

    FROM cteGames

    WHERE RowNum <= 5; --Limit five games per team

    GO

    --Clean my database

    --DROP TABLE Games;

    --DROP TABLE Teams;

    Additional reference: https://msdn.microsoft.com/en-us/library/ms175972.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • g.debrucker

    SSC Veteran

    Points: 292

    Thanks a lot! I'm very eager to learn new things so this will be no different with SQL.

    I see you don't use a distinct or Limit5 on the date column. So this sql snipper will return the last 5 games of a team?

  • Luis Cazares

    SSC Guru

    Points: 183524

    This will return the last five games for each team.

    If you want to return the last five games for a single team, you can have a simpler approach (or approaches).

    DECLARE @Team int;

    SET @Team = 1; --Define the team

    SELECT TOP (5) --Return only 5 rows

    g.GameID,

    g.GameDate,

    t.TeamName,

    g.GoalsScored,

    g.GoalsAgainst

    FROM Games g

    JOIN Teams t ON g.TeamID = t.TeamID

    WHERE g.TeamID = @Team --Filter the team

    ORDER BY g.GameDate DESC; --Define the order to get consistent results

    --This will work on SQL Server 2012 and more recent

    SELECT

    g.GameID,

    g.GameDate,

    t.TeamName,

    g.GoalsScored,

    g.GoalsAgainst

    FROM Games g

    JOIN Teams t ON g.TeamID = t.TeamID

    WHERE g.TeamID = @Team --Filter the team

    ORDER BY g.GameDate DESC --Define the order to get consistent results

    OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY; --Return only 5 rows

    Reference: https://msdn.microsoft.com/en-us/library/ms188385.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply