[SOLVED] help need to tweak this query...

[jasonc]

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

 

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.

 

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

 

my table looks like this...

 

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 

 

 

thanks

[phpweb]

I am fairly new, but could you use Max(hits)?

[jasonc]

hi

 

the hits part works ok, it is the datesent part that i need help with

 

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

[Barand]

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

[jasonc]

i tried that and it shows an error that

 

Unknown table 'e' in field list

[waynew]

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(hits) as hits
        FROM entries
        GROUP BY yrmonth
    ) as x USING (yrmonth, hits)

 

Not sure if this will work but give it a try. I used Barands code and got rid of the table alias.

[Barand]

Sorry 'bout that, I shouldn't have used the "e." in the subquery. I just copied the expression from the outer query and forgot to edit.

[jasonc]

i tried that and also noticed if i am correct missing '  in the code?

 

but now i get the error..

 

Column 'hits' in field list is ambiguous

 

 

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(hits) as hits

        FROM entries

        GROUP BY yrmonth

    ) as x USING (yrmonth, hits)

 

 

also when i use the above i get this too

 

 

'database_name.entries.yrmonth' in 'on clause'

 

 

i get the Column 'hits' in field list is ambiguous error when i take out the 'entries.' from the code

 

 

using the code and not adding the ' to the two places i get this error...

 

near '%m) as yrmonth, MAX.....

[waynew]

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)

[jasonc]

ok now i get this error

 

 

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

[jasonc]

just in case....

 

MySQL version  4.1.21-standard

[Barand]

you can only use

 

USING (yrmonth, max_hits)

 

when there are columns of that name in both tables

[jasonc]

ok so what should these be?

 

i only know very basic mysql commands. this has gone right over my head.

 

[Barand]

try

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

[jasonc]

added the '  after both of the %m

 

 

Unknown column 'DB_name.e.yrmonth' in 'on clause'

 

using

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

[fenway]

Yes, of course, because aliasing is done at the last step.

[jasonc]

?  no idea what that is

 

what is wrong with the code that causes the error to happen?

[Barand]

plan B

SELECT e.id, e.hits, e.user, DATE_FORMAT(e.datesent, '%Y-%m') as `yrmonth`
FROM entires e
INNER JOIN (
        SELECT DATE_FORMAT(datesent, '%Y-%m') as `yrmonth`, MAX(hits) as hits
        FROM entires
        GROUP BY `yrmonth`
    ) as x ON DATE_FORMAT(e.datesent, '%Y-%m') = x.`yrmonth`  AND e.hits = x.hits

[jasonc]

plan B

SELECT e.id, e.hits, e.user, DATE_FORMAT(e.datesent, '%Y-%m') as `yrmonth`
FROM entires e
INNER JOIN (
        SELECT DATE_FORMAT(datesent, '%Y-%m') as `yrmonth`, MAX(hits) as hits
        FROM entires
        GROUP BY `yrmonth`
    ) as x ON DATE_FORMAT(e.datesent, '%Y-%m') = x.`yrmonth`  AND e.hits = x.hits

 

thats the one,  cheers, thanks so much for your help