OK, turns out this was a two fold issue.
The server I was on had 4.0 mySQL, which was severely limited in it's
abilities to use subqueries. The server has since been update to the 4.1
series, and now the following 2-subquery query work just fine.
SELECT firstname, lastname, B.playerid, count(B.playerid) as Bubbles, B.playerid from
(SELECT firstname, lastname, amount, playerid, date, place FROM
(SELECT firstname, lastname, amount, results.playerid, date, place
FROM results, players WHERE players.playerid = results.playerid and amount=0 AND
year(date)=$year
ORDER BY DATE, place ASC ) as A GROUP BY A.date)
AS B GROUP BY B.playerid ORDER BY Bubbles DESC;
Thomas 'Skip' Hollowell wrote:
> How do I simply find out who the bubble is now in my db now that we
> track more than 1 $0 person. I need just the person with $0 in the
> amount column with the lowest Place for each date.
> I can iterate through it all if needed in PHP, but I am always looking
> to learn more SQL tricks.
>
> I am close, as this gives me 1 person, and it says what that amount is,
> but then it gives me the person with the highest PLACE, instead of what
> is shown in min(place). (
>
> SELECT playerid, date, place, min(place)
> FROM results WHERE amount=0 AND year(date)=2006
> GROUP BY date ORDER BY DATE, place DESC
>
> Full Query
> SELECT firstname, lastname, A.playerid, count(A.playerid) AS
> Bubbles, A.place
> FROM
> (SELECT playerid, date, place, min(place)
> FROM results WHERE amount=0 AND year(date)=2006
> GROUP BY date ORDER BY DATE, place DESC
> ) AS A,
> players WHERE a.playerid = players.playerid GROUP BY A.playerid
> ORDER BY Bubbles DESC;
>
> Anyone?
>
> Skip.
>
>
