Select where [field] are different and a little more (is it difficult... ?)

Discussion in 'MySQL' started by ticatoc, Sep 26, 2007.

0
  1. #1
    Hello,

    I have this select :

    select word, count(*) mostpopular from table group by word order by mostpopular DESC LIMIT 0 , 50
    Code (markup):
    My table is like that :
    word ¦ date ¦ author

    Actually my select select the most popular words in my table.
    I'd like that it select the mostpopular words but with a filter on the authors : I 'd like that if a word has the same poster twice it count for 1 only.

    Bonus : Then I 'd like to select only after a xx date. I know how to do this (hopefully ;-). But is it possible to set this at the same time I set a different time "inteval" for the "same poster" filter.
    For example : select the most popular words in the past 3 days where same posters count for 1 for 6 hours.

    I really don't know how to manage this :-?
     
    ticatoc, Sep 26, 2007 IP
  2. bluegrass special

    bluegrass special Peon

    Messages:
    790
    Likes Received:
    50
    Best Answers:
    0
    Trophy Points:
    0
    #2
    This works in SQL Server and is supposed to work in later versions of MySQL:

    Change count(*) to count(distinct author)
     
    bluegrass special, Sep 26, 2007 IP
  3. ticatoc

    ticatoc Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It seems to work with MySQL now :) !

    Thank you very much :) !!
     
    ticatoc, Sep 26, 2007 IP