Delete specific row in sql without referring to ID

Question

Example : I have insert 3 same value data ( a , a ,a ) into database

In database

a

a

a

How can I only remove the 2nd row's 'a' by referring to the row number.

Answer 1

Using slqlite3 your options are

"DELETE from example_table where example_column = 'a';"

this however removes the all of the rows with an 'a' in it which isnt what you want. what you can do is a search for all the 'a' bits. then get the id of the second one then delete where that id is.

I am assuming by your question that you mean you don't know the id and not that you don't have an id column. (which you really should do or you database will be come very slow and unusable)

To implement my solution:

// get the id of the second a row.
 Cursor c = db.rawQuery("SELECT id from example_table where example_column = a ;");
// iterate and get 2nd row
if (c.moveToPosition(1)){ // positions start at 0 in programming
   int id = c.getInt(0);
   db.exec("DELETE from example_table where id ="+id+";");
}