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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
