1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Sql query

Discussion in 'SQL PL/SQL' started by dkoracle, Jul 4, 2012.

  1. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I have two column in a table.
    my data.
    col1 col2
    1 2
    2 1
    7 8
    8 7

    example
    In above data delete either 1,2 or 2,1.

    requeried output:
    col1 col2
    1 2
    7 8

    help me on this.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Post your sql script what have you tried upto now.
     
  3. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    hi,
    I want sql query.

    regards,
    dk
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Post your sql query that you tried upto now. And what output you got?
     
  5. dkoracle

    dkoracle Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    I got out put.

    select distinct least(col1,col2) col1 , greatest(col2,col1) col2 from test;

    thaks for ur support.

    regards,
    dk.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is nice to see the folks sharing the result in the forum.
    Thanks for sharing.
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    If you want to delete those records then we can write the below pl/sql to do.

    Code (Text):


    SELECT * FROM test;
    7 rows selected
    [B]output: -[/B]
    col1 col2
    --- ----
    1   2
    7   8
    11  21
    2   1
    8   7
    21  11
    9   3

    DECLARE
    b number;
    BEGIN
    SELECT floor(count(*)/2) into b FROM test;
    DBMS_OUTPUT.PUT_LINE(b);
    for i in 1..b loop
    DELETE test WHERE COL2 IN(SELECT COL2 FROM test WHERE COL2 IN(SELECT COL1 FROM test)AND ROWNUM<2);
    DBMS_OUTPUT.PUT_LINE(sql%rowcount||' Row deleted');
    END LOOP;
    END;
    /

    after running the above program we can see the output.

    SELECT * FROM test;
    7 rows selected
    [B]output: -[/B]
    col1 col2
    --- ----
    1   2
    7   8
    11  21
    9   3

     
    If we run this pl/sql program then those records which col2 is having col1 those will be deleted.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This can also be done in 11g without PL/SQL:

    Code (SQL):
    SQL> CREATE TABLE test(
      2          a       NUMBER,
      3          b       NUMBER
      4  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO test
      3  VALUES (1,2)
      4  INTO test
      5  VALUES (7,8)
      6  INTO test
      7  VALUES (11,21)
      8  INTO test
      9  VALUES (2,1)
     10  INTO test
     11  VALUES (8,7)
     12  INTO test
     13  VALUES (21,11)
     14  INTO test
     15  VALUES (9,3)
     16  SELECT * FROM dual;
     
    7 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM test
      2  ORDER BY a;
     
             A          B
    ---------- ----------
             1          2
             2          1
             7          8
             8          7
             9          3
            11         21
            21         11
     
    7 ROWS selected.
     
    SQL>
    SQL> SELECT a, b, rank() OVER (ORDER BY a)
      2  FROM test;
     
             A          B RANK()OVER(ORDERBYA)
    ---------- ---------- --------------------
             1          2                    1
             2          1                    2
             7          8                    3
             8          7                    4
             9          3                    5
            11         21                    6
            21         11                    7
     
    7 ROWS selected.
     
    SQL>
    SQL> MERGE INTO test t
      2          USING ( SELECT r.a, r.b, r.rk FROM (SELECT a, b, rank() OVER (ORDER BY a) rk FROM test) r WHERE MOD(rk,2) = 0) x
      3          ON (x.a = t.a)
      4  WHEN matched THEN
      5          UPDATE SET t.b = x.b
      6          DELETE WHERE t.b = x.b;
     
    3 ROWS merged.
     
    SQL>
    SQL> SELECT * FROM test
      2  ORDER BY a;
     
             A          B
    ---------- ----------
             1          2
             7          8
             9          3
            21         11
     
    SQL>
    It does go through an apparently unnecessary update (necessary to get the delete recognized) but it doesn't rely on PL/SQL to get the correct rows deleted.
     
    Bharat likes this.
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This makes the required update statement useful:

    Code (SQL):
    SQL> MERGE INTO test t
      2          USING ( SELECT r.a, r.b, r.rk FROM (SELECT a, b, rank() OVER (ORDER BY a) rk FROM test) r WHERE MOD(rk,2) = 0) x
      3          ON (x.a = t.a)
      4  WHEN matched THEN
      5          UPDATE SET t.b = NULL
      6          DELETE WHERE t.b IS NULL;
     
    3 ROWS merged.
     
    SQL>
    SQL> SELECT * FROM test
      2  ORDER BY a;
     
             A          B
    ---------- ----------
             1          2
             7          8
             9          3
            21         11
     
    SQL>
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    This is not a generic method. ranking based on rownumber may not always work. see the bwlow example.

    Code (SQL):
    SQL> SELECT * FROM tst;

             A          B
    ---------- ----------
             1          2
             7          8
             3          4
            11         21
             6          5
             9          3
             2          1
             8          7
            21         11

    9 ROWS selected.

    SQL> MERGE INTO tst t
       USING ( SELECT r.a, r.b, r.rk FROM (SELECT a, b, rank() OVER (ORDER BY a) rk FROM tst) r WHERE MOD(rk,2) = 0) x
       ON (x.a = t.a)
       WHEN matched THEN
       UPDATE SET t.b = NULL
        DELETE WHERE t.b IS NULL;  2    3    4    5    6

    4 ROWS merged.

    SQL> SELECT * FROM tst;

             A          B
    ---------- ----------
             1          2
             7          8
             3          4
             9          3
            21         11

    SQL>
    Here One extra row (a = 6 ; b= 5) is deleted by merge.

    The actual rows are supposed to be as follows..
    Code (SQL):

             A          B
    ---------- ----------
             1          2
             7          8
             3          4
             6          5
             9          3
            21         11
     
  11. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Dear Raj,

    Then which solution we can use here in this case and to deal in all kind of cases. Is pl/sql is suitable for all kind of situations. Can you please suggest that.
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is already shared the by the OP.

     
  13. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    In the first OP he asked about how to delete them right! For that Iam asking what should we need to write?
     
  14. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It can be done by deleting the rows based on rowid as below.

    Code (SQL):
    SQL>  SELECT  least(a,b) a , greatest(a,b) a , rowid  FROM tst;

             A          A ROWID
    ---------- ---------- ------------------
             1          2 AAFiMTABhAAGcD1AAA
             7          8 AAFiMTABhAAGcD1AAB
             3          4 AAFiMTABhAAGcD1AAC
            11         21 AAFiMTABhAAGcD1AAD
             5          6 AAFiMTABhAAGcD1AAE
             3          9 AAFiMTABhAAGcD1AAF
             1          2 AAFiMTABhAAGcD1AAG
             7          8 AAFiMTABhAAGcD1AAH
            11         21 AAFiMTABhAAGcD1AAI

    9 ROWS selected.

    SQL> SELECT  least(a,b) a , greatest(a,b) a , MIN( rowid)   FROM tst
    GROUP BY least(a,b)  , greatest(a,b)   2  ;

             A          A MIN(ROWID)
    ---------- ---------- ------------------
             7          8 AAFiMTABhAAGcD1AAB
             3          4 AAFiMTABhAAGcD1AAC
             5          6 AAFiMTABhAAGcD1AAE
             3          9 AAFiMTABhAAGcD1AAF
             1          2 AAFiMTABhAAGcD1AAA
            11         21 AAFiMTABhAAGcD1AAD

    6 ROWS selected.

    SQL> DELETE FROM tst WHERE
    rowid NOT  IN (
    SELECT   MIN( rowid)   FROM tst
    GROUP BY least(a,b)  , greatest(a,b) )  2    3    4  ;

    3 ROWS deleted.

    SQL>  SELECT * FROM tst;

             A          B
    ---------- ----------
             1          2
             7          8
             3          4
            11         21
             6          5
             9          3

    6 ROWS selected.

    SQL>
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    In my haste to get an answer to the question I neglected to extend the data set and see if my possible solution still worked. Thank you for catching my error.