Get all rows apart from first and last in MySQL

MySQLMySQLi Database

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)
raja
Published on 30-Dec-2019 11:24:26
Advertisements