Deleting the nth row mysql

Question

I'm using MySQL v5.0.45 and trying to delete the nth row in a table irrespective of its ID number. I set up a prototype in PHP before integrating it into my web development project where I set up the following in my database:

CREATE TABLE prototype_1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name varchar(30));

INSERT INTO prototype_1 (name) VALUES ('A');
INSERT INTO prototype_1 (name) VALUES ('B');
INSERT INTO prototype_1 (name) VALUES ('C');

Simple enough! Now I tried using 'LIMIT' but I get the following error:

"ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'"

Now I understand you cannot use LIMIT in subqueries, however, is there not some sort of work around?

I can select the nth row and display it nicely with the following:

SELECT * FROM prototype_1 LIMIT 1,1;

Thus returning 'B' but why cannot I delete!?

What do you mean "irrespective of its ID number"? – Solomon Ucko May 18 '16 at 23:34 — Solomon Ucko, May 18 '16 at 23:34

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

You need a little trick like this

delete from prototype_1 where id = (select id from (select id from prototype_1 order by id limit 1,1) as t)

From docs

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants

(...)

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 03 '11 at 16:53

Nicola Cossu

54,599
15
92
98

I was unable to find a lot of documentation on working around this problem and this worked perfectly! Thank you so much! :D – Riyan Mar 03 '11 at 17:02
Marvelous, just marvelous! The one thing I would request is that you spell out in your answer that `prototype_1` is the table name, and `t` is the "nth" row in the table. – Kenn Sebesta Jul 07 '17 at 16:55

score 0 · Answer 2 · answered Oct 04 '18 at 14:09

0

Delete the n-th record like this. Make sure you order the result by the column you want, in this case the id column:

DELETE FROM prototype_1 ORDER BY id LIMIT n-1,1

answered Oct 04 '18 at 14:09

renatodamas

16,555
8
30
51

Deleting the nth row mysql

2 Answers2