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.
--
-- Table structure for table `results`
--
CREATE TABLE results (
ID int(11) NOT NULL auto_increment,
playerId int(11) default '0',
date datetime default NULL,
locationId int(11) default NULL,
place int(11) default '0',
amount float default '0',
buyin int(5) default NULL,
rebuy enum('Y','N') default NULL,
gametype varchar(255) default 'Texas Hold Em',
numbuyins int(4) default NULL,
points double default '0',
PRIMARY KEY (ID),
KEY locationId (locationId),
KEY playerId (playerId)
) TYPE=MyISAM;
--
-- Table structure for table `players`
--
CREATE TABLE players (
playerId int(11) NOT NULL auto_increment,
firstName varchar(50) default NULL,
nickName varchar(50) default NULL,
lastName varchar(50) default NULL,
PRIMARY KEY (playerId)
) TYPE=MyISAM;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
