CODEWITHSUNDEEP
sqldatabaseoracle-database
anjali
anjali

Reputation: 11

Delete top 1 row using SQL

I have a SQL, the purpose is to keep the last two entries and delete the top rows . 

delete from table 
where RowID in (select top 10 RowID from table)

This deletes all rows instead of the first rows I intend to delete.

In the interface I am using, 'WITH' command does not work. It has to be something like the above query.

I have a table with 3 columns x, y, z. I can't rely on the pseudo column rownum as when I delete some rows the rownum don't change. As this delete query will be running every 60 sec and the rownum of the table won't start from 1 every time.

I want to delete all other rows except the last two entries. Top will work 

delete from custom.colperformance 
where RowID in (select top 2 RowID 
                from custom.colperformance 
                order by RowID desc)

This is giving me an error 

    Table structure 

ProfileTime   TriggerTime  RowId
12            3             4
12            5             6
6             7             2

here Rowid comes in random if we delete some rows in between

Please help!! .. thanks in advance

Upvotes: 0

Views: 29144

Answers (4)

user21116997
user21116997

Reputation: 1

You can do this using the below code

Let the table name be 'demo' then the code should be:

DELETE from demo where id = (SELECT id FROM demo limit 1);

You do not need to provide id manually. Make sure to order the table accordingly.

Upvotes: 0

DarekK
DarekK

Reputation: 83

DELETE FROM table_name LIMIT 1

Upvotes: 1

Atilla Ozgur
Atilla Ozgur

Reputation: 14701

If this is oracle you can not use TOP 10, use following syntax:

delete from table where RowID in (select RowID from table where rownum <= 10)

Of course you should also give order by 

delete from table where RowID in (select RowID from table where rownum <= 10 ORDER BY table.columnX)

Upvotes: 4

Barrie van Boven
Barrie van Boven

Reputation: 199

first select the top 1 from the entire table, then select the top one from the entire table where you leave out the result of the first query

select top 1 (RowID) 
from table 
where RowID NOT IN (select top 1 RowID from table)

now you know which rows you do not want to delete. save in temp table maybe?

Upvotes: 0

Related Questions