List:General Discussion« Previous MessageNext Message »
From:Ian Date:December 17 2009 12:25pm
Subject:Re: Simple Query Question
View as plain text  
Hi,

Thanks, I just checked and it was a memcache that was caching the output.
See I knew it was a simple solution ;)

Thanks for the effort everyone and sorry for wasting time.

Regards
Ian

2009/12/17 Aleksandar Bradaric <leannonn@stripped>

> Hi Ian,
>
> Why do you think something's wrong? Here is my test data and the results of
> your query:
> ---
> mysql> SELECT * FROM wp_views;
> +---------+---------+------------+-------+
> | blog_id | post_id | date       | views |
> +---------+---------+------------+-------+
> |       1 |       1 | 2009-12-16 |     2 |
> |       1 |       1 | 2009-12-17 |     3 |
> |       1 |       2 | 2009-12-16 |     4 |
> |       1 |       2 | 2009-12-17 |     5 |
> |       2 |       1 | 2009-12-16 |     6 |
> |       2 |       1 | 2009-12-17 |     7 |
> |       2 |       2 | 2009-12-16 |     8 |
> |       2 |       2 | 2009-12-17 |     9 |
> |       1 |       1 | 2009-12-18 |     1 |
> |       1 |       2 | 2009-12-18 |     1 |
> |       2 |       1 | 2009-12-18 |     1 |
> |       2 |       2 | 2009-12-18 |     1 |
> +---------+---------+------------+-------+
> 12 rows in set (0.00 sec)
>
> mysql> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE
> (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id
> ORDER BY views DESC LIMIT 10;
> +---------+---------+-------+
> | blog_id | post_id | views |
> +---------+---------+-------+
> |       2 |       2 |    17 |
> |       2 |       1 |    13 |
> |       1 |       2 |     9 |
> |       1 |       1 |     5 |
> +---------+---------+-------+
> 4 rows in set (0.00 sec)
> ---
>
> Seems OK to me... Are you getting different results?
>
>
> Take care,
> Aleksandar
>
>
>
> Ian wrote:
>
>> Hi,
>>
>> I am sure there is a simple solution to this problem, I just cant find it
>> :)
>>
>> I have got a table that records views for an article for each blog per
>> day.
>> So the structure is as follows:
>>
>> CREATE TABLE `wp_views` (
>> `blog_id` int(11) NOT NULL,
>> `post_id` int(11) NOT NULL,
>> `date` date NOT NULL,
>> `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>>
>> Now thats fine and I can pull top blogs per day and thats all fine, but
>> what
>> I am after is pulling the top articles for a time period and where I am
>> running into problems is where two blogs have the same post_id's the views
>> get sum()'d for the day and I cant figure out (read end of year mind
>> block)
>> how to get around it. Here is my current query (for last 7 days):
>>
>> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date
>> <=
>> "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY
>> views DESC LIMIT 10
>>
>> Any ideas as to whats wrong. I know its something simple, I just cant put
>> my
>> finger on it.
>>
>> Thanks in advance,
>> Ian
>>
>>
>

Thread
Simple Query QuestionIan17 Dec
  • Re: Simple Query QuestionAleksandar Bradaric17 Dec
    • Re: Simple Query QuestionIan17 Dec