DB Query not running as wanted

My goal is to choose the highest record(By SALE) from table partition by company(CNAME) and delete other records.

create table Tn(cname varchar(30), sale int);

Table Values:

 CNAME          SALE
 1. Apple   4000
 2. Apple   5000
 3. Apple   4200 
 4. Samsung 3800
 5. Samsung 3900
 6. Samsung 3000
 7. Samsung 3200
 8. Nokia   800
 9. Nokia   2000
 10. Xiaomi 1100
 11. Xiaomi 700
 12. Lenovo 600
 13. Lenovo 200
 14. Lenovo 100

My Code:

with Cte as
(
  select cname,sale, dense_rank() over(partition by cname order by sale desc)as Ranker 
  from Tn
)
delete from cte where Ranker>1

select * from Tn;

Now this issue is I’m getting

ORA-00928: missing SELECT keyword

this error if I don’t use this select * from Tn;

Even if I use I see no changes in the table.

I’m Oracle SQL Live platform to use my query. Please suggest the issue of my query.

Please suggest any other way without using Common Table Expression.

UPDATE

I have tried out this also:

delete from tn 
where sale not in (select max(sale)as A 
                   from tn 
                   group by cname 
                   order by A desc);

But it is saying

ORA-00907: missing right parenthesis

Answer

If you want to delete from original table then CTE will not help you.

You will need something like following:

Delete from tn where rowid in
(Select rid from
(select cname,
        sale, 
        dense_rank() over(partition by cname order by sale desc)as Ranker, 
        rowid as rid
  from Tn) 
     where ranker > 1)

Cheers!!

Advertisement

Answer