Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: Query with in clause

    Hi I am trying to do a select as below



    Table A
    Id Code
    11 104
    12 104

    Table B
    Id Code Reason
    11 104 aa
    12 104 aa
    12 104 bb

    I basically want to join A & B but want to get any one distinct value from Table B such that my output is as below, does not matter which reason code I pick. Please help!

    Id Code Reason
    11 104 aa
    12 104 aa

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So you could take any; one option is to use MIN or MAX functions. Here are two examples:
    Code:
    SQL> select a.id, a.code, max(b.reason)
      2  from tab_a a, tab_b b
      3  where a.id = b.id
      4    and a.code = b.code
      5  group by a.id, a.code;
    
            ID       CODE MAX(B.REAS
    ---------- ---------- ----------
            12        104 bb
            11        104 aa
    
    SQL>
    SQL> select a.id, a.code, b.reason
      2  from tab_a a, tab_b b
      3  where a.id = b.id
      4    and a.code = b.code
      5    and b.reason = (select max(b1.reason)
      6                    from tab_b b1
      7                    where b1.id = b.id
      8                      and b1.code = b.code
      9                   );
    
            ID       CODE REASON
    ---------- ---------- ----------
            11        104 aa
            12        104 bb
    
    SQL>

  3. #3
    Join Date
    Feb 2009
    Posts
    25
    Thank you so much that worked like a charm!

Posting Permissions

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