Get all rows apart from first and last in MySQL

To get all rows apart from first and last, use subquery along with MIN() and MAX(). Let us first create a table −

mysql> create table DemoTable1917    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentCode int,    StudentMarks int    ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1917(StudentCode,StudentMarks) values(78,95); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode,StudentMarks) values(78,96); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode,StudentMarks) values(78,97); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode,StudentMarks) values(78,98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1917(StudentCode,StudentMarks) values(78,99); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1917;

This will produce the following output −

+-----------+-------------+--------------+ | StudentId | StudentCode | StudentMarks | +-----------+-------------+--------------+ |         1 |          78 |           95 | |         2 |          78 |           96 | |         3 |          78 |           97 | |         4 |          78 |           98 | |         5 |          78 |           99 | +-----------+-------------+--------------+ 5 rows in set (0.00 sec)

Here is the query to get all rows apart from first and last:

mysql> select * from DemoTable1917    where StudentId NOT IN    (    (    select min(StudentId) from DemoTable1917 where StudentCode=78),    (select max(StudentId) from DemoTable1917 where StudentCode=78)    ) and StudentCode=78;

This will produce the following output −

+-----------+-------------+--------------+ | StudentId | StudentCode | StudentMarks | +-----------+-------------+--------------+ |         2 |          78 |           96 | |         3 |          78 |           97 | |         4 |          78 |           98 | +-----------+-------------+--------------+ 3 rows in set (0.00 sec)