tweak of query, to correct a slight error in results.

[newbeee]

SELECT e.id, e.clicks, e.submittedby, DATE_FORMAT( e.submitgmtdatetime, '%Y-%m' ) AS `yrmonth`
FROM sites e
INNER JOIN (
SELECT DATE_FORMAT( submitgmtdatetime, '%Y-%m' ) AS `yrmonth` , MAX( clicks ) AS clicks
FROM sites
GROUP BY `yrmonth` DESC
) AS x ON DATE_FORMAT( e.submitgmtdatetime, '%Y-%m' ) = x.yrmonth
AND e.clicks = x.clicks
WHERE e.submittedby != 'admin'

 

I get these results

 

id clicks submittedby yrmonth

123 407 admin 2008-09

131 414 user1 2008-10

167 420 user2 2008-11

753 334 admin 2008-12

 

 

but i would like to have the results where the 'admin' is not in the resultsbut the next user that is not the 'admin' that has the highest number of clicks.  and for each month.

[newbeee]

table structure...

CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL auto_increment,
  `submitgmtdatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `clicks` int(11) NOT NULL default '0',
  `confirmed` char(1) NOT NULL default '0',
  `submittedby` text NOT NULL,
  `confirmedby` text NOT NULL,
  KEY `id` (`id`)
)

[xtopolis]

That's weird, I get these results:

[pre]

id    clicks    submittedby    yrmonth

167 420      user2           2008-11

131 414      user1           2008-10

[/pre]

 

Using your structure

CREATE TABLE `sites` (
  `id` int(11) NOT NULL auto_increment,
  `submitgmtdatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `clicks` int(11) NOT NULL default '0',
  `confirmed` char(1) collate latin1_german2_ci NOT NULL default '0',
  `submittedby` text collate latin1_german2_ci NOT NULL,
  `confirmedby` text collate latin1_german2_ci NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=754 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=754 ;

and this test data(copied from your output you said you got)

INSERT INTO `sites` VALUES (123, '2008-09-01 00:00:00', 407, '0', 'admin', '');
INSERT INTO `sites` VALUES (131, '2008-10-01 00:00:00', 414, '0', 'user1', '');
INSERT INTO `sites` VALUES (167, '2008-11-01 00:00:00', 420, '0', 'user2', '');
INSERT INTO `sites` VALUES (753, '2008-12-01 00:00:00', 334, '0', 'admin', '');

I'm using Mysql 5.0

[fenway]

but i would like to have the results where the 'admin' is not in the resultsbut the next user that is not the 'admin' that has the highest number of clicks.  and for each month.

Isn't this continued for another topic???

 

And I have no idea what you mean.