3 Replies Latest reply on Jan 14, 2015 7:45 AM by Tobias Arnhold

    Generate some kind of cartesian list

    Tobias Arnhold

      I have a simple list which I have to combine which itself.

       

      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.197');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.198');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.199');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.202');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.203');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.204');
      INSERT INTO MY_TAB ( ID ) VALUES ('10.253.14.205');
      

       

      Now I wan to generate a combination of each IP address.

      But what I get is this:

       SELECT
        S1.ID as A,
        S2.ID as B
        FROM MY_TAB S1
        CROSS JOIN MY_TAB S2
        WHERE S1.ID != S2.ID
      

       

      Data:

      10.253.14.197    10.253.14.198

      10.253.14.197    10.253.14.199

      10.253.14.197    10.253.14.202

      10.253.14.197    10.253.14.203

      10.253.14.197    10.253.14.204

      10.253.14.197    10.253.14.205

      10.253.14.198    10.253.14.197

      10.253.14.198    10.253.14.199

      10.253.14.198    10.253.14.202

      10.253.14.198    10.253.14.203

      10.253.14.198    10.253.14.204

      10.253.14.198    10.253.14.205

      10.253.14.199    10.253.14.197

      10.253.14.199    10.253.14.198

      10.253.14.199    10.253.14.202

      10.253.14.199    10.253.14.203

      10.253.14.199    10.253.14.204

      10.253.14.199    10.253.14.205

      10.253.14.202    10.253.14.197

      10.253.14.202    10.253.14.198

      10.253.14.202    10.253.14.199

      10.253.14.202    10.253.14.203

      10.253.14.202    10.253.14.204

      10.253.14.202    10.253.14.205

      10.253.14.203    10.253.14.197

      10.253.14.203    10.253.14.198

      10.253.14.203    10.253.14.199

      10.253.14.203    10.253.14.202

      10.253.14.203    10.253.14.204

      10.253.14.203    10.253.14.205

      10.253.14.204    10.253.14.197

      10.253.14.204    10.253.14.198

      10.253.14.204    10.253.14.199

      10.253.14.204    10.253.14.202

      10.253.14.204    10.253.14.203

      10.253.14.204    10.253.14.205

      10.253.14.205    10.253.14.197

      10.253.14.205    10.253.14.198

      10.253.14.205    10.253.14.199

      10.253.14.205    10.253.14.202

      10.253.14.205    10.253.14.203

      10.253.14.205    10.253.14.204

       

      Looking for IP ending with 197 I want:

      10.253.14.197    10.253.14.198

      10.253.14.197    10.253.14.199

      10.253.14.197    10.253.14.202

      10.253.14.197    10.253.14.203

      10.253.14.197    10.253.14.204

      10.253.14.197    10.253.14.205

       

      I don't want the opposite combination:

      10.253.14.198    10.253.14.197

      10.253.14.199    10.253.14.197

      .. and so on

       

      Hope for help.

       

      Best regards,


      Tobias

        • 1. Re: Generate some kind of cartesian list
          Marwim

          But what I get is this:

          1. SELECT 
          2.   S1.ID as A, 
          3.   S2.ID as
          4.   FROM MY_TAB S1 
          5.   CROSS JOIN MY_TAB S2 
          6.   WHERE S1.ID != S2.ID 

           

          Hello,

           

          try s1.id < s2.id

           

          Regards

          Marcus

          • 2. Re: Generate some kind of cartesian list
            Karthick2003

            Like this

             

            SQL> with t
              2  as
              3  (
              4  select rownum rno, id
              5    from my_tab
              6  )
              7  select t.id, t1.id
              8    from t
              9    join t t1
             10      on t.rno < t1.rno;
            
            ID                   ID
            -------------------- --------------------
            10.253.14.197        10.253.14.198
            10.253.14.197        10.253.14.199
            10.253.14.197        10.253.14.202
            10.253.14.197        10.253.14.203
            10.253.14.197        10.253.14.204
            10.253.14.197        10.253.14.205
            10.253.14.198        10.253.14.199
            10.253.14.198        10.253.14.202
            10.253.14.198        10.253.14.203
            10.253.14.198        10.253.14.204
            10.253.14.198        10.253.14.205
            10.253.14.199        10.253.14.202
            10.253.14.199        10.253.14.203
            10.253.14.199        10.253.14.204
            10.253.14.199        10.253.14.205
            10.253.14.202        10.253.14.203
            10.253.14.202        10.253.14.204
            10.253.14.202        10.253.14.205
            10.253.14.203        10.253.14.204
            10.253.14.203        10.253.14.205
            10.253.14.204        10.253.14.205
            
            21 rows selected.
            
            SQL>
            
            1 person found this helpful
            • 3. Re: Generate some kind of cartesian list
              Tobias Arnhold

              Thanks for the help.