delete query

Database
Enthusiast

delete query

Hi all need a delete query... 

i was able to write sel query ... partition by col1 and col2 desc... and loading into temp tbl... i was able to do that ...

but interview asked me to write a query to acvhieve o/p.. without loading into temp table

 

delete_query.JPG

4 REPLIES
Enthusiast

Re: delete query

Query Working fine : 

DEL FROM EMPLOYEE WHERE (COL1,COL2) NOT IN (
SEL COL1, COL2 FROM (
SEL COL1, COL2 FROM EMPLOYEE
QUALIFY ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 DESC)=1)D)

 

Syntax error: Ordered Analytical Functions are not allowed in subqueries.
DELETE Command Failed.

 

DELETE FROM EMPLOYEE WHERE (COL1,COL2) IN (
SELECT A.COL1,A.COL2 FROM (SEL EMPLOYEE.COL1, EMPLOYEE.COL2 FROM EMPLOYEE
QUALIFY COUNT(*) OVER (PARTITION BY EMPLOYEE.COL1 ORDER BY EMPLOYEE.COL2) GT 1)A
QUALIFY ROW_NUMBER() OVER (PARTITION BY A.COL1 ORDER BY A.COL2)=1)

 

 

 

can you please explain why the second query got failed

Junior Contributor

Re: delete query


Syntax error: Ordered Analytical Functions are not allowed in subqueries.


Subquery... IN ( SELECT ...)

Derived Table = ... FROM (SELECT ...) AS dt

 

Your first query wrapped the OLAP function in a Derived Table, while query #2 applies QUALIFY within the Subquery :-)

Enthusiast

Re: delete query

below should work

DELETE FROM TABLE WHERE (COL1,COL2) IN (SEL COL1, MIN(COL2) FROM TABLE GROUP BY 1 HAVING COUNT(*)>1)

ram
Fan

Re: delete query

Hi try this query.. it is working fine

 

SyntaxEditor Code Snippet

delete from employee1 c where (col1,col2) not in 
(select a.col1,a.col2 from(select col1,col2 from employee1
qualify row_number() over(partition by col1 order by col2 desc)=1)a)