November 16, 2015 at 7:04 am
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 😀
November 16, 2015 at 7:31 am
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
November 16, 2015 at 7:39 am
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!
November 16, 2015 at 7:51 am
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
November 16, 2015 at 7:54 am
for the moment MS Access, But have the possibility to switch over to MySQL.
November 16, 2015 at 7:57 am
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.
November 16, 2015 at 8:01 am
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
November 16, 2015 at 8:29 am
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
November 16, 2015 at 9:21 am
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?
November 16, 2015 at 9:40 am
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
November 23, 2021 at 10:28 am
This was removed by the editor as SPAM
September 6, 2022 at 7:02 am
This was removed by the editor as SPAM
September 20, 2023 at 12:33 pm
This was removed by the editor as SPAM
September 20, 2023 at 12:35 pm
This was removed by the editor as SPAM
September 20, 2023 at 12:38 pm
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply