Forum Stats

  • 3,840,086 Users
  • 2,262,565 Discussions
  • 7,901,147 Comments

Discussions

help required to wrire a query.

2»

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    [email protected] wrote:
    Thanks Alex...that works!!
    Than you can mark the thread as answered..
  • Dird
    Dird Member Posts: 1,225
    edited Dec 4, 2009 4:03AM
    Hi,
    I tried and it seems similar/the same to Alex's but deletes 0 rows :/
    delete from deltest2 where rowid in
    (select rowid from (select rowid, name, id, status, row_number() over (partition by name, id order by status) rank from deltest) where rank > 1 ) 
    When I tried rowid = 'specific rowid' too it didn't like rowid :X perhaps it's some issue with apex~

    Edit: bah I was referencing two different tables...I knew I shouldn't have made a backup copy :/

    Mike

    Edited by: Dird on Dec 4, 2009 9:03 AM
  • Roy4321
    Roy4321 Member Posts: 305 Bronze Badge
    we cannot use the min(rowid)...say for eg.. we take the record mat...here suppose the record with id as 1 was inserted after record with id 2 ..it will delete the record with id 1...we will have to use the row_number()..
  • Roy4321
    Roy4321 Member Posts: 305 Bronze Badge
    Thanks for all the comments.
  • 523888
    523888 Member Posts: 208
    Got your point ... dat was stupid of me :)
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Dec 8, 2009 6:15AM
    create table test as
    select 'mat' name, 123 id, 1 status from dual union all
    select 'mat' name, 123 id, 2 status from dual union all -- to be deleted
    select 'mat' name, 123 id, 2 status from dual union all -- to be deleted
    select 'sam' name, 456 id, 2 status from dual union all
    select 'sam' name, 456 id, 2 status from dual union all -- to be deleted
    select 'dan' name, 789 id, 1 status from dual union all
    select 'dan' name, 789 id, 1 status from dual union all -- to be deleted
    select 'dan' name, 789 id, 2 status from dual union all -- to be deleted
    select 'pan' name, 111 id, 1 status from dual union all
    select 'XXX' name, 999 id, 9 status from dual union all -- to be deleted
    select 'XXX' name, 999 id, 9 status from dual union all -- to be deleted
    select 'XXX' name, 999 id, 1 status from dual;
    Hahaha I like multi columns in predicate B-)
    delete from test
    where (RowID,1) not in(
    select RowID,Row_Number() over(partition by name,id
                                   order by status)
      from test);
    
    SQL> select*from test;
    
    NAM         ID     STATUS
    ---  ---------  ---------
    mat        123          1
    sam        456          2
    dan        789          1
    pan        111          1
    XXX        999          1
    We can use below solution,too :D
    delete from test
    where RowID not in(select max(RowID)
                              Keep(Dense_Rank First
                                   order by status)
                         from test
                       group by name,id);
This discussion has been closed.