Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2014
    Posts
    11

    Unanswered: How to write query for desire output?

    I will have to choose only those row who contain single 2 from given column col_id, please write sql qury here:


    Col_id
    11, 2, 3, 22
    5, 6, 12, 22
    4, 23, 14, 22
    2, 9, 22, 30
    8, 18, 19, 2


    Output:

    Col_id
    11, 2, 3, 22
    2, 9, 22, 30
    8, 18, 19, 2

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2014
    Posts
    11
    I need output caused I am not getting our answer anywhere.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried so far?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by aaditya321 View Post
    I need output caused I am not getting our answer anywhere.
    Yes, because you are not supplying the information being requested. Supply a test case, supply your database version. Make sure that your test case contains an example of all of the expected possibilities (or at least, the majority of the possible cases). For example, is the following string possible:
    2, 4, 34, 2, 22
    (i.e. can the value 2 appear twice in your string?)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by pablolee View Post
    ...
    ... Make sure that your test case contains an example of all of the expected possibilities (or at least, the majority of the possible cases). For example, is the following string possible:
    2, 4, 34, 2, 22
    (i.e. can the value 2 appear twice in your string?)
    Other examples:
    11, 2 ,3, 22
    (zero or 2 or more blanks between a comma and a following number)
    5, 6, 22 , 02
    (2 with leading zero)
    5, 6, 12, 22 , 2
    (more than 4 items)
    2, 3
    (less than 4 items)
    56789012, 123456789, 12, 22 , 2
    (more than two digits number(s). how long the column col_id?)
    so on...
    Last edited by tonkuma; 04-01-14 at 10:47.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without a better problem definition, I guess any working solution will do. I'll offer:
    Code:
    SELECT Col_id
       FROM (VALUES ('11, 2, 3, 22')
    ,  ('5, 6, 12, 22')
    ,  ('4, 23, 14, 22')
    ,  ('2, 9, 22, 30')
    ,  ('8, 18, 19, 2')) AS PseudoTable(Col_id)
       WHERE  Col_id IN ('11, 2, 3, 22', '2, 9, 22, 30', '8, 18, 19, 2');
    Please let us know if that addresses your problem, or if you can clarify the definition of the problem as we requested in the earlier posts.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Pat, that statement is not valid for Oracle (Oracle does not support the VALUES row constructor outside of an INSERT statement)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You guys are putting too much time into a solution for an ill defined, untried issue. Just refer the op to the answers on page 178.
    Dave

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by shammat View Post
    Pat, that statement is not valid for Oracle (Oracle does not support the VALUES row constructor outside of an INSERT statement)
    Considering that it is "air code" composed on a cell phone, I'm not sure that it will work anywhere but I think that it should.

    I guess that I assumed that any problems would show up in testing. I really hoped for a clarification rather than just running off with the first SQL offered.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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