Friday, June 17, 2011

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);

No comments:

Post a Comment