Advanced query help

--0015175cb1d409cdf604911d1c78
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I've the following query

SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
teams_id ,players_id ,rating ,IF(@team <> teams_id, @row := 1, @row := @row
+ 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
,players.players_id ,players_master.rating FROM players 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 AND
players.teams_id > 0 ORDER BY players.teams_id, players_master.rating DESC)
s1) s2 WHERE rank <= 11 GROUP BY teams_id ORDER BY total_team_rating DESC

I'm running MySQL 5.0.77. However, if I run this query nothing is returned,
but if I run the query again I get the desired results. Why is this ?

Cheers
Neil

--0015175cb1d409cdf604911d1c78--

Re: Advanced query help

--0016e6d26cc8a1cc7d0491384096
Content-Type: text/plain; charset=ISO-8859-1

At a guess, because you use @team in an if statement before you actually
define it.


On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil <
neil.tompkins@googlemail.com> wrote:

> Hi,
>
> I've the following query
>
> SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
> teams_id ,players_id ,rating ,IF(@team <> teams_id, @row := 1, @row := @row
> + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
> ,players.players_id ,players_master.rating FROM players 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 AND
> players.teams_id > 0 ORDER BY players.teams_id, players_master.rating DESC)
> s1) s2 WHERE rank <= 11 GROUP BY teams_id ORDER BY total_team_rating DESC
>
> I'm running MySQL 5.0.77. However, if I run this query nothing is
> returned,
> but if I run the query again I get the desired results. Why is this ?
>
> Cheers
> Neil
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e6d26cc8a1cc7d0491384096--

Re: Advanced query help

--0015175cda041eb9e604913aa6e6
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I did try defining it before the IF statement, but still the same ?

Cheers
Neil

On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman wrote:

> At a guess, because you use @team in an if statement before you actually
> define it.
>
>
> On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil <
> neil.tompkins@googlemail.com> wrote:
>
>> Hi,
>>
>> I've the following query
>>
>> SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT
>> teams_id ,players_id ,rating ,IF(@team <> teams_id, @row := 1, @row :=
>> @row
>> + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
>> ,players.players_id ,players_master.rating FROM players 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 AND
>> players.teams_id > 0 ORDER BY players.teams_id, players_master.rating
>> DESC)
>> s1) s2 WHERE rank <= 11 GROUP BY teams_id ORDER BY total_team_rating DESC
>>
>> I'm running MySQL 5.0.77. However, if I run this query nothing is
>> returned,
>> but if I run the query again I get the desired results. Why is this ?
>>
>> Cheers
>> Neil
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--0015175cda041eb9e604913aa6e6--

Re: Advanced query help

--0016363b87c6642c4e04913acfbc
Content-Type: text/plain; charset=ISO-8859-1

Then you'll probably need to define it with a separate select before using
it. I'm half-guessing here, really, but that sounds like it makes sense :-)

On Mon, Sep 27, 2010 at 11:49 AM, Tompkins Neil <
neil.tompkins@googlemail.com> wrote:

> Hi,
>
> I did try defining it before the IF statement, but still the same ?
>
> Cheers
> Neil
>
> On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman > >wrote:
>
> > At a guess, because you use @team in an if statement before you actually
> > define it.
> >
> >
> > On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil <
> > neil.tompkins@googlemail.com> wrote:
> >
> >> Hi,
> >>
> >> I've the following query
> >>
> >> SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM
> (SELECT
> >> teams_id ,players_id ,rating ,IF(@team <> teams_id, @row := 1, @row :=
> >> @row
> >> + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id
> >> ,players.players_id ,players_master.rating FROM players 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
> AND
> >> players.teams_id > 0 ORDER BY players.teams_id, players_master.rating
> >> DESC)
> >> s1) s2 WHERE rank <= 11 GROUP BY teams_id ORDER BY total_team_rating
> DESC
> >>
> >> I'm running MySQL 5.0.77. However, if I run this query nothing is
> >> returned,
> >> but if I run the query again I get the desired results. Why is this ?
> >>
> >> Cheers
> >> Neil
> >>
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016363b87c6642c4e04913acfbc--