On 9/9/2010 3:57 AM, Tompkins Neil wrote:
> Any help would be really appreciated ?
>
>
>
> ---------- Forwarded message ----------
> From: Tompkins Neil <neil.tompkins@stripped>
> Date: Wed, Sep 8, 2010 at 5:30 PM
> Subject: Query SUM help
> To: "[MySQL]" <mysql@stripped>
>
>
> Hi
>
> I've the following query :
>
> SELECT total_team_rating, my_teams_id
> FROM
> (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS
> total_team_rating
> FROM players
> INNER JOIN players_master ON players.players_id = players_master.players_id
> WHERE players.worlds_id = 1 AND players.red_cards = 0 AND
> players.injury_duration_remaining = 0
> GROUP BY players.teams_id) s1
> ORDER BY s1.total_team_rating DESC
>
> This gives me the total of players_master.rating for each players.teams_id.
> However, I'm wanting to only base the players_master.rating on the top 11
> records in the players table for each team. How can I modify my query to
> achieve this ?
>
> Thanks
> Neil
>
The meat of your problem is the "top 11" players part. The SQL language
operates on sets, not sequences. In order to find the "top 11" you
need to somehow identify them explicitly so that you can process them as
a set of values.
Do you have a column on your `players` table that sequences (from 1..n)
the players in the order you want them ranked? If not, you will need to
add that data to your `players` table (or build a temporary table with
that information in it), then pick the top 11, then work on their SUM()-s.
Can you not just filter out the top 11 in your client code from the
query that includes all players totals?
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
