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!

Can we query vertical columns?

Discussion in 'SQL PL/SQL' started by preston, Apr 30, 2012.

  1. preston

    preston Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    My table structure is shown below. I want to delete dublicated question_uids. However I only want to delete one of them. But I don't want to delete if the answer is > 0 or Yes. If their answers equal it doesn't matter which one is deleted but If the answers is not same I want delete garbage answer which is 0 or 'No'.

    So, instead of 2 question_uid rows I want single question_uid row. In other words, I want question_uids unique.



    Code (Text):

    question_uid   answer_id answer
    -------------------------------------
    2308100294  328487  0    (For example this question_uid's answers are equal, it doesn't matter which is deleted)
    2308100294  328495  0    (For example this question_uid's answers are equal, it doesn't matter which is deleted)
    2308100307  328457  0
    2308100307  328475  0
    2308100349  328416  0
    2308100349  328421  0
    2308100356  328538  0
    2308100356  328545  0
    2308200276  328496  0
    2308200276  328517  0
    2308200307  328458  0
    2308200307  328462  0
    2308200349  328385  0
    2308200349  328406  0
    2308200356  328526  0
    2308200356  328555  0
    2406600282  328455  0     (this should be deleted)
    2406600282  328459  100  (This shouldn't be deleted)
    2406600383  328404  200
    2406600383  328430  0
    2502100043  328393  No
    2502100043  328426  No
    2502100300  328491  No
    2502100300  328497  Yes
    2502100314  328436  No
    2502100314  328456  Yes
    2502100382  328539  No
    2502100382  328547  No
    2502100385  328568  No
    2502100385  328578  No
    2502600117  328641  No
    2502600117  328654  No
    2502600127  328657  No
    2502600127  328659  No
     
    So which statement should I write for this situation, except doing it row by row manually?

    THANKS FOR YOUR TIME.
    YOURS FAITHFULLY
     
  2. Alan_S

    Alan_S Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am by no means an expert (just joined yesterday) but how I would do this is to create a temp table of the dups (distinct question_uid) where count(*) of question_uid>1 and then delete function where answer = 0 and yourtable.question_uid=temptable.question_uid as the key or
    create a temp table of the dups (distinct question_uid) where count(*) of question_uid>1,
    create a new field "delete?" and update this field as 'Y' where yourtable.question_uid=temptable.question_uid and answer = 0 else 'N' then create new table where new field "delete?" = 'N'. Use the second method if you don't like deleting.

    Hope this helps!
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Given the nature of your data you will probably need a two-pass approach as illustrated here:

    Code (SQL):
     
    SQL> CREATE TABLE quest_ans(
      2  question_uid NUMBER,
      3  answer_id NUMBER,
      4  answer varchar2(5)
      5  );
     
     
    TABLE created.
     
    Elapsed: 00:00:00.01
    SQL>
    SQL> INSERT ALL
      2  INTO quest_ans
      3  VALUES(2308100294,328487,'0')
      4  INTO quest_ans
      5  VALUES(2308100294,328495,'0')
      6  INTO quest_ans
      7  VALUES(2308100307,328457,'0')
      8  INTO quest_ans
      9  VALUES(2308100307,328475,'0')
     10  INTO quest_ans
     11  VALUES(2308100349,328416,'0')
     12  INTO quest_ans
     13  VALUES(2308100349,328421,'0')
     14  INTO quest_ans
     15  VALUES(2308100356,328538,'0')
     16  INTO quest_ans
     17  VALUES(2308100356,328545,'0')
     18  INTO quest_ans
     19  VALUES(2308200276,328496,'0')
     20  INTO quest_ans
     21  VALUES(2308200276,328517,'0')
     22  INTO quest_ans
     23  VALUES(2308200307,328458,'0')
     24  INTO quest_ans
     25  VALUES(2308200307,328462,'0')
     26  INTO quest_ans
     27  VALUES(2308200349,328385,'0')
     28  INTO quest_ans
     29  VALUES(2308200349,328406,'0')
     30  INTO quest_ans
     31  VALUES(2308200356,328526,'0')
     32  INTO quest_ans
     33  VALUES(2308200356,328555,'0')
     34  INTO quest_ans
     35  VALUES(2406600282,328455,'0')
     36  INTO quest_ans
     37  VALUES(2406600282,328459,'100')
     38  INTO quest_ans
     39  VALUES(2406600383,328404,'200')
     40  INTO quest_ans
     41  VALUES(2406600383,328430,'0')
     42  INTO quest_ans
     43  VALUES(2502100043,328393,'No')
     44  INTO quest_ans
     45  VALUES(2502100043,328426,'No')
     46  INTO quest_ans
     47  VALUES(2502100300,328491,'No')
     48  INTO quest_ans
     49  VALUES(2502100300,328497,'Yes')
     50  INTO quest_ans
     51  VALUES(2502100314,328436,'No')
     52  INTO quest_ans
     53  VALUES(2502100314,328456,'Yes')
     54  INTO quest_ans
     55  VALUES(2502100382,328539,'No')
     56  INTO quest_ans
     57  VALUES(2502100382,328547,'No')
     58  INTO quest_ans
     59  VALUES(2502100385,328568,'No')
     60  INTO quest_ans
     61  VALUES(2502100385,328578,'No')
     62  INTO quest_ans
     63  VALUES(2502600117,328641,'No')
     64  INTO quest_ans
     65  VALUES(2502600117,328654,'No')
     66  INTO quest_ans
     67  VALUES(2502600127,328657,'No')
     68  INTO quest_ans
     69  VALUES(2502600127,328659,'No')
     70  SELECT * FROM dual;
     
    34 ROWS created.
     
    Elapsed: 00:00:00.03
    SQL>
    SQL> commit;
     
    Commit complete.
     
    Elapsed: 00:00:00.00
    SQL>
    SQL> SELECT * FROM quest_ans;
     
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2308100294     328487 0
      2308100294     328495 0
      2308100307     328457 0
      2308100307     328475 0
      2308100349     328416 0
      2308100349     328421 0
      2308100356     328538 0
      2308100356     328545 0
      2308200276     328496 0
      2308200276     328517 0
      2308200307     328458 0
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2308200307     328462 0
      2308200349     328385 0
      2308200349     328406 0
      2308200356     328526 0
      2308200356     328555 0
      2406600282     328455 0
      2406600282     328459 100
      2406600383     328404 200
      2406600383     328430 0
      2502100043     328393 No
      2502100043     328426 No
     
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2502100300     328491 No
      2502100300     328497 Yes
      2502100314     328436 No
      2502100314     328456 Yes
      2502100382     328539 No
      2502100382     328547 No
      2502100385     328568 No
      2502100385     328578 No
      2502600117     328641 No
      2502600117     328654 No
      2502600127     328657 No
     
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2502600127     328659 No
     
    34 ROWS selected.
     
    Elapsed: 00:00:00.03
    SQL>
    SQL> DELETE FROM quest_ans
      2  WHERE rowid IN (SELECT MIN(rowid) FROM quest_ans
      3  GROUP BY question_uid
      4  HAVING COUNT(*) > 1)
      5  AND answer IN ('0','No');
     
    16 ROWS deleted.
     
    Elapsed: 00:00:00.03
    SQL>
    SQL> DELETE FROM quest_ans
      2  WHERE rowid IN (SELECT MAX(rowid) FROM quest_ans
      3  GROUP BY question_uid
      4  HAVING COUNT(*) > 1)
      5  AND answer IN ('0','No');
     
    1 ROW deleted.
     
    Elapsed: 00:00:00.01
    SQL>
    SQL> SELECT * FROM quest_ans;
     
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2308100294     328495 0
      2308100307     328475 0
      2308100349     328421 0
      2308100356     328545 0
      2308200276     328517 0
      2308200307     328462 0
      2308200349     328406 0
      2308200356     328555 0
      2406600282     328459 100
      2406600383     328404 200
      2502100043     328426 No
     
    QUESTION_UID  ANSWER_ID ANSWE
    ------------ ---------- -----
      2502100300     328497 Yes
      2502100314     328456 Yes
      2502100382     328547 No
      2502100385     328578 No
      2502600117     328654 No
      2502600127     328659 No
     
    17 ROWS selected.
     
    Elapsed: 00:00:00.01
    SQL>
     
    Notice that one duplicate was left after the first pass as the answer is 200 for the row with the lower rowid value; a second pass using max(rowid) gets the errant '0' answer row deleted. Notice also that no temporary table is needed.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Two-pass approach is not needed. A single straight Query like below one would be enough.
    Code (SQL):

     SELECT * FROM quest_ans x
     WHERE   x.rowid NOT IN  (  SELECT  MAX(rowid)
                           FROM quest_ans y  
                           WHERE y.answer  IN ('0','No')        
                           GROUP BY question_uid
                           HAVING COUNT(*) > 1 )
     AND answer  IN ('0','No')
     
  5. vprog

    vprog Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Ghaziabad
    select question_uid, answer_id, answer from <table-name> where answer > 0 || answer != "no";