Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2011
    Posts
    13

    Unanswered: Please, help me in drafting the query

    I have table with entries as follows

    Col-1 Col-2 Col-3 Col-4 Col-5

    Fname loginID uniqueID logintime duration
    abc a12 1234 2011_06_22_00_00 4
    fcl a12 1234 2011_06_22_00_00 4
    happu a12 1234 2011_06_22_00_15 4
    samle a12 1234 2011_06_22_00_00 4
    abc b12 234 2011_06_22_00_10 4
    abar b12 234 2011_06_22_00_00 4
    samt b12 234 2011_06_22_00_15 4
    acer c2 1239 2011_06_22_00_00 4
    sony c2 1239 2011_06_22_00_00 4

    Result format:
    list of Fname's group wise....
    I need those Fname's having same loginID, uniqueID, logintime, duration but different Fname's

    Query should give the results in this format
    set-1
    abc
    fcl
    samle
    set-2
    happu
    set-3
    abc
    abr
    set-4
    samt
    set-5
    acer
    sony

  2. #2
    Join Date
    May 2011
    Posts
    11
    Hi Chavala,

    Try this,

    select * from Tbl_name
    where uniqueID=uniqueId and loginID=loginID
    group by Fname,loginID
    order by loginID,logintime

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chavala View Post
    I need those Fname's having same loginID, uniqueID, logintime, duration but different Fname's
    Code:
    SELECT t.Fname
      FROM ( SELECT loginID
                  , uniqueID
                  , logintime
                  , duration 
               FROM daTable
             GROUP
                 BY loginID
                  , uniqueID
                  , logintime
                  , duration 
             HAVING COUNT(DISTINCT Fname) > 1 ) AS x
    INNER
      JOIN daTable AS t
        ON t.loginID   = x.loginID    
       AND t.uniqueID  = x.uniqueID  
       AND t.logintime = x.logintime 
       AND t.duration  = x.duration
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2011
    Posts
    13
    Thank you,

    But i need ouputs in a set of groups...not as single column....
    each match should give a set of feature names....

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chavala View Post
    But i need ouputs in a set of groups...not as single column....
    sorry, but that is not what you asked for

    you clearly said "I need those Fname's having ..."

    if you want "set of groups" you will have to explain what this means
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2011
    Posts
    13
    Thank you for your help........NP
    Reason why i need in a groups is that....
    each group of Fname will results into one unique catagory

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chavala View Post
    Thank you for your help........NP
    Reason why i need in a groups is that....
    each group of Fname will results into one unique catagory
    so...

    add some more columns to the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2011
    Posts
    13
    other approach will be....

    col-1 col-2
    A 1
    A 2
    A 3
    B 3
    B 2

    I want to write a query which can give me out as A if values in col-2 is 1,2,3

    I have written a query
    select col-1 from table where col-2='1' and col-2 = '2' and col-3 = '3';
    When i execute the query I am getting empty string.....
    I think we may not be able to write AND operator for same Column....
    I don't know how to solve this....

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chavala View Post
    I think we may not be able to write AND operator for same Column....
    well, actually, you ~can~ write it, but as you have discovered, it always returns no rows

    that's because a single column value cannot be equal to more than one thing at a time

    the solution is to use GROUP BY, so that you are writing conditions in the HAVING clause that pertian to all the rows in the group

    the conditions will involve counting the separate values, and then ANDing the conditions such that each count has to be greater than 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2011
    Posts
    13
    Thank you,
    I am unable to trace the condition to write in Having clause...
    counting the seperate values..?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by chavala View Post
    select col-1 from table where col-2='1' and col-2 = '2' and col-3 = '3';
    the specifications are right here

    for each col1 (which is your GROUP BY column), you want to count at least one '1' in col2, and at least one '2' in col2, and at least one '3' in col2

    that's what you want to count

    for example,
    Code:
    COUNT(CASE WHEN col2='1' THEN 'hola' END)
    will give you the count of rows where col2='1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2011
    Posts
    13
    Thank you...

Posting Permissions

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