Multiple LIMIT statements in a query

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

I’m out of things to try. Any ideas? Thanks

yes, there is a way

you can use LIMIT on the outer query only

for the subquery, use the method that has proven successful for decades (in all databases, even those that don’t support LIMIT)

if you would kindly show the actual query you would use for the subquery, using LIMIT if you want to, i can rewrite it for you

Here is an example of the simplified query

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.

Thanks

“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 :smile:

your description of your t3 table seems to suggest that each gameweek has its own id

what are the ~real~ table layouts, please?

Sorry, my bad. Table T3 is more like

gameid
gameweek
hometeam
visitor

So in gameweek 9 there would be 13 or 14 games

i totally understand… more than you know

i run a database-driven nfl pool on my web site… here’s a screen shot from next week’s picks –

the spreads are all 0.5 because they haven’t been set yet

the “esreveR” button allows you to make your picks and then reverse them (for those guys like me who unfailingly always pick the wrong team)

aha, this is fantasy stats. Picture didn’t come through though

so getting back to your “square brackets” concept

it’s not the last 3 games, is it

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. :smile:

the last 3 what for each player?

it’s obviously not going to be t3, is it

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.

Does that make a difference?

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

:blush:

Have fun. And whenever you get a chance to look at it I really appreciate it.

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. :grinning: