Re: help need to tweak this query...

[jasonc310771]

i have the following query which works but only for one type of date range.


SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2008-10%' ORDER BY 'hits' DESC LIMIT 1


i have my datesent like this in other entries...

'2008-10-12'
'2007-11-06'
'2007-06-27'

and so on

but would like to have just the entries of the most hits in each month.

but all on one query instead of using the for/next for each possible month that has gone by!

could someone kindly advise what the best query would be to do this.


like this...

SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2006-01%' ORDER BY 'hits' DESC LIMIT 1
...
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2006-08%' ORDER BY 'hits' DESC LIMIT 1
...
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2006-10%' ORDER BY 'hits' DESC LIMIT 1
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2006-12%' ORDER BY 'hits' DESC LIMIT 1
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2007-03%' ORDER BY 'hits' DESC LIMIT 1
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2007-06%' ORDER BY 'hits' DESC LIMIT 1
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2007-07%' ORDER BY 'hits' DESC LIMIT 1
....
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2008-10%' ORDER BY 'hits' DESC LIMIT 1
SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2008-11%' ORDER BY 'hits' DESC LIMIT 1



but in one query for all months that are in the DB

my table structure...

CREATE TABLE IF NOT EXISTS `entries` (
`id` int(11) NOT NULL auto_increment,
`datesent` datetime NOT NULL default '0000-00-00 00:00:00',
`hits` int(11) NOT NULL default '0',
`user` text NOT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6086


i have tried this but get an error...



SELECT entries.id, entries.hits, entries.user, DATE_FORMAT(entries.datesent, '%Y-%m') as yrmonth
FROM entries
INNER JOIN (
SELECT DATE_FORMAT(entries.datesent, '%Y-%m') as yrmonth, MAX(entries.hits) as max_hits
FROM entries
GROUP BY yrmonth
) as x USING (yrmonth, max_hits)


Unknown column 'mydatabase_name.entries.max_clicks' in 'on clause'

[masterofollies]

Try this?

SELECT id, hits, user FROM `entires` WHERE `datesent` LIKE '2006-01%' AND LIKE '2006-06% AND LIKE '2006-12%'' ORDER BY 'hits' DESC LIMIT 1

[jasonc310771]

ah yes but that only get the entries for those datesent's i put in the query. i need to have just 1 entries for each month where hits is the highest for just that month.

and for every. without having to put ...


2006-01, 2006-02,...... , 2006-11, 2006-12
2007-01, 2006-02....
...
...

for every month that has pasted so far and that could in the furture, this would be a very large query!

[shyam]

select e.id, e.hits, e.user, date_format(e.datesent, '%Y-%m') as yrmonth
from entries e
where e.hits = (select max(m.hits) from entries m
where date_format(e.datesent, '%Y-%m') = date_format(m.datesent, '%Y-%m'));

[jasonc310771]

aarrgghh i crashed my hosts server :-( sent it in to a loop or something

select e.id, e.hits, e.user, date_format(e.datesent, '%Y-%m') as yrmonth
from entries e
where e.hits = (select max(m.hits) from entries m
where date_format(e.datesent, '%Y-%m') = date_format(m.datesent, '%Y-%m'));

[jasonc310771]

where can i learn to do this myself?

i have checked out mysql.com and can not find as yet any ref, to e.id, e.hits.....

what are these and what sites can anyone suggest i look at to learn this method?