How to select all rows from a table except the last one in MySQL?
You need to use != operator along with subquery. The syntax is as follows −
select *from yourTableName where yourIdColumnName != (select max(yourIdColumnName)
from yourTableName );
To understand the above syntax, let us create a table. The query to create a table is as follows −
mysql> create table AllRecordsExceptLastOne
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> UserName varchar(10),
-> UserAge int
-> ,
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.65 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('John',21);
Query OK, 1 row affected (0.12 sec)
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('Carol',28);
Query OK, 1 row affected (0.18 sec)
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('Mike',22);
Query OK, 1 row affected (0.13 sec)
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('Sam',29);
Query OK, 1 row affected (0.14 sec)
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('David',27);
Query OK, 1 row affected (0.11 sec)
mysql> insert into AllRecordsExceptLastOne(UserName,UserAge) values('Larry',24);
Query OK, 1 row affected (0.20 sec)Display all records from the table using a select statement. The query is as follows −
mysql> select *from AllRecordsExceptLastOne;
The following is the output −
+----+----------+---------+
| Id | UserName | UserAge |
+----+----------+---------+
| 1 | John | 21 |
| 2 | Carol | 28 |
| 3 | Mike | 22 |
| 4 | Sam | 29 |
| 5 | David | 27 |
| 6 | Larry | 24 |
+----+----------+---------+
6 rows in set (0.00 sec)
Here is the query to select all rows from a table except the last one −
mysql> select *from AllRecordsExceptLastOne where Id!=(select max(Id) from
AllRecordsExceptLastOne);
The following is the output −
+----+----------+---------+
| Id | UserName | UserAge |
+----+----------+---------+
| 1 | John | 21 |
| 2 | Carol | 28 |
| 3 | Mike | 22 |
| 4 | Sam | 29 |
| 5 | David | 27 |
+----+----------+---------+
5 rows in set (0.04 sec)
Published on 27-Feb-2019 15:19:20
