I know that this subject has been done to death but I really cannot implement any of the suggestions in the context I need it. I currently have a query that show a League Standings table $lgetable = mysql_query("SELECT * ,COUNT(*) AS ct , t.team_name as Tm , @rownum := @rownum+1 AS rank , (sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS P , count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP , sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W , sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D , sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L , SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF , SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA , (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD from teams t join all_games g on t.team_id in (g.home_team,g.away_team) WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31' GROUP BY t.team_id ORDER BY P desc, GD desc, GF desc (The alias's used are $ht = "g.home_team = t.team_id"; $at = "g.away_team = t.team_id"; $hw = "g.home_goals > g.away_goals"; $aw = "g.home_goals < g.away_goals"; $d = "g.home_goals = g.away_goals"; $hg ="g.home_goals"; $ag ="g.away_goals" I am trying to use that same query to show only the most recent five games for each team (t.team_id) which I thought would be possible just by removing the current ORDER BY statement and replacing it with HAVING ct <= 5 ORDER BY date DESC But this causes no rows to be returned. Strangely, as a test, I grouped it by date instead of t.team_id, and it returned results, though obviously not the right ones. Am I simply missing something with the grouping, or am I miles from the solution? Thanks Steve

Jump to content

Existing user? Sign In
Sign In

Remember me Not recommended on shared computers

Forgot your password?
Sign Up

Browse
- Forums
- Staff
- Leaderboard
- Guidelines
- Terms of Service
- More
Activity
- All Activity
- Search
- More
Join our Discord!
More
- More

MySQL Help

Show Last N records per group

By MargateSteve
February 28, 2011 in MySQL Help

Share

https://forums.phpfreaks.com/topic/229075-show-last-n-records-per-group/

Followers 0

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

× Pasted as rich text. Restore formatting

Only 75 emoji are allowed.

× Your link has been automatically embedded. Display as a link instead

× Your previous content has been restored. Clear editor

× You cannot paste images directly. Upload or insert images from URL.

Insert image from URL

×

Share

https://forums.phpfreaks.com/topic/229075-show-last-n-records-per-group/

Followers 0

Go to topic listing

×

Browse
- Back
- Forums
- Staff
- Leaderboard
- Guidelines
- Terms of Service
Activity
- Back
- All Activity
- Search
Join our Discord!

×

Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.

Sign In

Show Last N records per group

Recommended Posts

ebmigue

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information