Skip to main content

Delete rows except MIN DATE

If you  want to write a delete query for a situation such as below..

Table : DBG.PUBS

Columns are CODE , PRICE , DATE1 , DATE2

001   50    05-JAN-11   05-FEB-11
002  100  01-JAN-11   06-MAR-11
003  40    05-APR-11   06-MAY-11



If you want to delete all the rows except the row which has the oldest DATE1...
you can use below query..



DELETE FROM DBG.PUBS
WHERE date1 != (SELECT MIN(date1)
                FROM DBG.PUBS);

Comments

Popular posts from this blog

Setting ORACLE_SID

The  Oracle System ID  ( S ID ) is used to uniquely identify a particular database on a system How to set ORACLE_SID: Windows: set ORACLE_SID=orcl Unix/ Linux: export ORACLE_SID=orcl SID is case sensitive in Unix / Linux environments. How to check the current ORACLE_SID: Windows: Go to the commnand prompt and type as C:\> set ORACLE_SID (This will show if any ORACLE_SID is already set). C:\> set (To know all the parameters set) Unix/ Linux: echo $ORACLE_SID