How to delete last record (on condition) from a table in MySQL?

To delete last record (on condition) from a table, you need to use ORDER BY DESC with LIMIT 

1. The syntax is as follows:

DELETE FROM yourTableName WHERE yourColumnName1=yourValue ORDER BY yourColumnName2 DESC LIMIT 1;

The above syntax will delete last record (on condition) from a table. It sorts the column in descending order and choose the first element to delete.

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table UserLoginTable    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserId int,    -> UserLoginDateTime datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.94 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-01-27 13:47:20'); Query OK, 1 row affected (0.19 sec) mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(1,'2018-11-28 12:30:12'); Query OK, 1 row affected (0.16 sec) mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-01-26 11:30:30'); Query OK, 1 row affected (0.20 sec) mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(1,'2015-03-11 15:23:55'); Query OK, 1 row affected (0.21 sec) mysql> insert into UserLoginTable(UserId,UserLoginDateTime) values(2,'2019-03-21 16:01:56'); Query OK, 1 row affected (0.23 sec)

Now you can display all records from the table using select statement. The query is as follows:

mysql> select *from UserLoginTable;

The following is the output:

+----+--------+---------------------+ | Id | UserId | UserLoginDateTime   | +----+--------+---------------------+ |  1 |      2 | 2019-01-27 13:47:20 | |  2 |      1 | 2018-11-28 12:30:12 | |  3 |      2 | 2019-01-26 11:30:30 | |  4 |      1 | 2015-03-11 15:23:55 | |  5 |      2 | 2019-03-21 16:01:56 | +----+--------+---------------------+ 5 rows in set (0.00 sec)

Here is the query to delete last record (on condition) from a table:

mysql> delete from UserLoginTable where UserId=2 ORDER BY UserLoginDateTime DESC LIMIT 1; Query OK, 1 row affected (0.16 sec)

Check the table record once again using select statement. The query is as follows:

mysql> select *from UserLoginTable;

The following is the output:

+----+--------+---------------------+ | Id | UserId | UserLoginDateTime   | +----+--------+---------------------+ |  1 | 2 | 2019-01-27 13:47:20 | | 2 | 1 | 2018-11-28 12:30:12 | | 3 | 2 | 2019-01-26 11:30:30 | | 4 | 1 | 2015-03-11 15:23:55 | +----+--------+---------------------+ 4 rows in set (0.00 sec)

Last record is now deleted from a table with UserId 2.