CODEWITHSUNDEEP
oracle-apex
aforz
aforz

Reputation: 49

why does not my query work in oracle apex?

This is my query but when I run this in oracle apex it gives me the following error:

delete from 

(select ename,e1.store_id,e1.sal as highest_sal 
from
employees e1 inner join 
(select store_id,max(sal) as sal
 from employees
 group by store_id
) e2
on e1.store_id=e2.store_id
and e1.sal=e2.sal
order by store_id) s

where rowid not in
(select min(rowid) from s
group by highest_sal);

The output is:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 673
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658
ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 659
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1829


4. (select store_id,max(sal) as sal
5.  from employees
6.  group by store_id
7. ) e2
8. on e1.store_id=e2.store_id

When I run the code in parentheses, which has the alias s alone, it runs without any problems, but when it is placed in this code, it gives an error

updated: My goal is to first group the data according to store_id and get the maximum sal in each, and join it to the main table itself where sal and store_id are the same, and display its name, which The resulting table is called s. Then I want to remove the duplicate rows from the table (which have the same sal) and to do this we group according to highest_sal and select the least rowid between them, and remove those rowId that are not in the subquery. As a result, non-duplicates are obtained. (This is a trick to remove duplicate lines.)

Upvotes: 0

Views: 367

Answers (1)

MT0
MT0

Reputation: 168026

You appear to want to delete all rows with the highest sal for each store_id grouping except for the row in each group with the lowest ROWID.

You can do that with analytic functions. Either:

DELETE FROM employees
WHERE  ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT RANK() OVER (PARTITION BY store_id ORDER BY sal DESC) AS rnk,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY sal DESC, ROWID ASC)
             AS rn
    FROM   employees
  )
  WHERE  rnk = 1
  AND    rn > 1
);

or:

DELETE FROM employees
WHERE  ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT sal,
           MAX(sal) OVER (PARTITION BY store_id) AS max_sal,
           MIN(ROWID) KEEP (DENSE_RANK LAST ORDER BY sal)
                      OVER (PARTITION BY store_id) AS min_rid_for_max_sal
    FROM   employees
  )
  WHERE  sal = max_sal
  AND    ROWID != min_rid_for_max_sal
);

Or, from Oracle 12, with row limiting clauses in a correlated sub-query:

DELETE FROM employees e
WHERE  ROWID IN (
  SELECT ROWID
  FROM   (
    SELECT sal
    FROM   employees x
    WHERE  e.store_id = x.store_id
    ORDER BY sal DESC
    FETCH FIRST ROW WITH TIES
  )
  ORDER BY ROWID
  OFFSET 1 ROW FETCH NEXT 100 PERCENT ROWS ONLY
);

Which, for the sample data:

CREATE TABLE employees (ename, store_id, sal) AS
SELECT 'A', 1, 1 FROM DUAL UNION ALL
SELECT 'B', 1, 2 FROM DUAL UNION ALL
SELECT 'C', 1, 3 FROM DUAL UNION ALL
SELECT 'D', 2, 1 FROM DUAL UNION ALL
SELECT 'E', 2, 2 FROM DUAL UNION ALL
SELECT 'F', 2, 2 FROM DUAL;

All delete the f row.

db<>fiddle here

Upvotes: 2

Related Questions