Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: How to query about all entries which occur less than n times

    Hello ALL,

    I have a table as follows

    Code:
    ID   Cate.  		Word
    1    Sports 		basketball
    2    Sports  		football
    3    SPorts  		volleyball
    
    4    Economy		share
    5    Economy		bank
    6    Economy		credit
    7    Economy		basketball
    
    8    Society   		credit
    9    Society		democracy
    10   Society 		people
    11   Society 		basketball
    I would like to query about those words which occur in less than 3 (<3) categories.
    In the above example, the query result should be all entries except for entries corresponding to 'basketball', i.e., all excluding entries 1, 7, 11.


    I used the following statement

    Code:
    Select *
     From TableName
    Group By
          Word
    Having Count(*)<3
    Order by
        Cate
    However, I got less entries than expected by this statement.
    For a word, only one entry is returned. e.g. for 'credit'

    I have

    Code:
    6    Economy		credit
    while I expect

    Code:
    6    Economy		credit
    8    Society   		credit
    it seems that the clause 'group by' lead to this result. How can I do to get the right answer?
    Please help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT *
      FROM TableName
     WHERE word IN
           ( SELECT word
               FROM TableName
             GROUP 
                 BY Word
             HAVING COUNT(*) < 3 )
    ORDER 
        BY Cate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My preference is to join instead of using the IN operator as a join will scale better
    Code:
    SELECT a.*
    FROM   table_name As a
     INNER
      JOIN (
            SELECT word
            FROM   table_name
            GROUP
                BY word
            HAVING Count(*) < 3
           ) As b
        ON a.word = b.word
    ORDER
        BY a.cate
    Last edited by gvee; 06-02-09 at 09:09.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one george

    pssst, fix your syntax error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fixed-d-d-d
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •