#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Counting but still showing all results


    Hello,

    I am trying to select all raws where some columns appear exactly X times.

    The table looks something like that:

    strain chr pos value1 value2
    b 1 121 666 66
    d 1 121 999 66
    c 3 200 777 33
    d 3 200 999 0
    a 1 121 434 22
    c 1 121 777 55
    ...

    I want to have a query that join over chr and pos but output the raws sorted by chr,pos,strain:

    X=4
    a 1 121 434 22
    b 1 121 666 66
    c 1 121 777 55
    d 1 121 999 66

    X=2
    c 3 200 777 33
    d 3 200 999 0

    Thank you for your help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,136
    Rep Power
    4274
    Originally Posted by qqq2395
    I am trying to select all raws where some columns appear exactly X times.
    which columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    chr and pos colums


    Thank you
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,136
    Rep Power
    4274
    Code:
    SELECT t.strain 
         , t.chr  
         , t.pos  
         , t.value1  
         , t.value2
      FROM ( SELECT chr
                  , pos
               FROM daTable
             GROUP
                 BY chr
                  , pos
             HAVING COUNT(*) = 3 -- value of X here
           ) AS dupes
    INNER
      JOIN daTable AS t
        ON t.chr = dupes.chr
       AND t.pos = dupes.pos
    ORDER
        BY t.chr  
         , t.pos  
         , t.strain
    vwalah
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Thank you!


    Originally Posted by r937
    Code:
    SELECT t.strain 
         , t.chr  
         , t.pos  
         , t.value1  
         , t.value2
      FROM ( SELECT chr
                  , pos
               FROM daTable
             GROUP
                 BY chr
                  , pos
             HAVING COUNT(*) = 3 -- value of X here
           ) AS dupes
    INNER
      JOIN daTable AS t
        ON t.chr = dupes.chr
       AND t.pos = dupes.pos
    ORDER
        BY t.chr  
         , t.pos  
         , t.strain
    vwalah


    It works great! :-)

IMN logo majestic logo threadwatch logo seochat tools logo