CODEWITHSUNDEEP
mysql
Arunkumar G
Arunkumar G

Reputation: 225

delete the last row in a table using sql query?

I am trying to delete the last record in the table named "marks" in the database using MySql query.
The query I tried for this is as follows:

DELETE MAX(`id`) FROM `marks`;

There are 8 columns in the table. I want to delete the last column without specifying id, as in:

DELETE FROM marks where id=8;

After deleting the 8th record, I want to delete the 7th; after that 6th and so on, up to 1st record without specifying the id manually.

Upvotes: 20

Views: 96505

Answers (5)

core2drew
core2drew

Reputation: 540

This query uses a subquery to identify the MAX(row_id) for each group_id and then deletes the rows matching both the group_id and the maximum row_id.

DELETE FROM your_table
WHERE (group_id, row_id) IN (
    SELECT group_id, MAX(row_id)
    FROM your_table
    GROUP BY group_id
);

Upvotes: 0

ANshul
ANshul

Reputation: 9

To delete 2nd Last Row

Delete from tablename where id in (select id from (select id from tablename order by id desc limit 1,1) as id);

By using other queries it will show multiple errors.

Upvotes: -1

Sanoj Dushmantha
Sanoj Dushmantha

Reputation: 1015

This is not the best but a different solution.

DELETE FROM marks 
WHERE  id = (SELECT id 
             FROM   marks 
             ORDER  BY id DESC 
             LIMIT  1);

Upvotes: 2

Chintan7027
Chintan7027

Reputation: 7605

@Abhshek's Answer is right and works for you but you can also try the following code if the id is not increment

DELETE FROM MARKS WHERE ID=(SELECT MAX(id) FROM MARKS)

Upvotes: 13

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If id is auto-increment then you can use the following 

delete from marks
order by id desc limit 1

Upvotes: 62

Related Questions