Is there a way to use 2 limit statements in the same query? One to limit the overall selection and the second to limit a subquery selection?
For example, there is a table of students and they take tests on different dates. So I want to see the avg score for the last 4 tests for the top 10 students.
Main table t1 would be id, name
Sub table t2 would be id, testdate, score
So I would need something like
select t1.name, avg(t2.score) as avgscore
from Main as t1
inner join Sub as t2 on t2.id = t1.id [LIMIT latest 4 -- can't do this but just for discussion]
group by t1.id
order by avg(t2.score) DESC
LIMIT 10
SELECT t1.playername, sum(t2.points), AVG(t2.points) as avgpoints, COUNT(t2.gameid) as games, GROUP_CONCAT(t3.gameweek) as gameweeks
FROM players as t1
INNER JOIN playerstats as t2 on t2.playerid = t1.playerid
INNER JOIN schedule as t3 on t3.gameid = t2.gameid [order by t3.gameweek DESC LIMIT 3]
WHERE t1.type = "xx"
GROUP BY t1.playerid
LIMIT 10
Inside the square brackets is where i would like to limit it to the last 3 games so they would need to be ordered in descending fashion (by gameweek) and then just use the first 3 (or whatever number) to do the calculations in the select.
The tables would be:
T1
playerid INT key
playername VARCHAR
T2
playerid INT key
gameid INT key
teamid INT
points INT
t3
gameid INT key
gameweek SMALLINT
If you need more clarification please let me know.
“last 3 games” based on gameweek desc would look like this –
SELECT gameid
, gameweek
FROM t3 AS this
WHERE ( SELECT COUNT(*)
FROM t3
WHERE gameweek > this.gameweek ) < 3
run this by itself to confirm that it’s working
then put the whole thing inside parentheses, give it a table alias, and use it as you would’ve used the t3 table
That doesn’t seem to work. If I run it I get all the gameid’s for the last week only. So all the games with gameweek 17.
Also, the issue is I can’t use gameweek as the sole factor because they don’t play every week. So in gameweek I would have weeks 1-17 but each team gets one week off (this is the NFL). So if I am in week 10 and I want the last 3 weeks, for one team that might be 9,8,7, for another 8,7,6 and for another 9,7,6 and so on.
There will be one record for each game in the playerstats table. I hope I am communicating this effectively. If not, let me know.
[quote=“bostboy, post:5, topic:207744, full:true”]
That doesn’t seem to work. If I run it I get all the gameid’s for the last week only. So all the games with gameweek 17. [/quote]
dude, not my fault
your description of your t3 table seems to suggest that each gameweek has its own id
That’s funny. A long time ago I used to bet football and it is much easier to go 0-8 than it ever is to go 8-0. But if you know you are going to click the reverse button you will subconsciously pick the other teams. hahahaha
So you should make the reverse button random, sometimes it reverses and sometimes not so you don’t know if you are going to reverse it. lol And for the most part that is the thinking that gets you to 0-8.
The last 3 for each player. And remember, a player might have been traded during those 3 games so it can’t be based on team. In T2 that is why I carry a gameid and a teamid. The teamid pertains to the current week’s team.
I know I can do it via programming but I like to try to push the db as far as possible first.
The last 3 point scores which are kept in t2. I am relating t2 to t3 because t3 has the game info.
Although I suppose the limit could consider the gameid’s and take the last 3 gameid’s (the 3 highest) from t2 because they are in order. Game number or week number can be gotten from t3 but the limit and sorting could be done on t2.
The gamied is not really supposed to carry any meaning but it does by default because the games are put into t3 in order by schedule. They don’t add games after the schedule is set.
Just FYI, t2 also carries all the statistics for the player for each game, one record per game. But for this discussion, that is irrelevant I think. All I am worried about right now is manipulating the points to get sum, avg, etc.
[quote=“bostboy, post:14, topic:207744, full:true”]
The last 3 point scores which are kept in t2…
the limit and sorting could be done on t2.[/quote]
can’t get to this until tomorrow because friday night party night
but i’m totes confident i can do it, once i’m thinking straight again
sorry for delay, here is “last 3 games for each player”
SELECT this2.playerid
, this2.gameid
, this2.teamid
, this2.pos
, this3.gameweek
FROM t2 AS this2
INNER
JOIN t3 AS this3
ON this3.gameid = this2.gameid
WHERE ( SELECT count(*)
FROM t2
INNER
JOIN t3
ON t3.gameid = t2.gameid
WHERE t2.playerid = this2.playerid
AND t3.gameweek > this3.gameweek ) < 3
ORDER
BY this2.playerid
, this2.gameid
, this3.gameweek
the ORDER BY is only so that you can verify the results – remove that clause when you use this as a subquery (your “square backets”)
No problem and thanks so much. I am out of town for the holiday but will try to hide away and take a look at this if I can get some time. Again, thanks so much and I will let you know as soon as I can get to it.
I ran the query by itself and it appears to work fine. I need some time to digest this because I have no idea why this works (as always); and to add it to the bigger query, but excellent. Thanks again and I will keep you posted on whether I get it all doing what I want.
