How to select first and last data row from a MySQL result?

You can select the first and last data row using MIN() and MAX(). The syntax is as follows −

SELECT *
FROM yourTableName
WHERE yourColumnName = (SELECT MIN(yourColumnName) FROM yourTableName)
UNION
SELECT *
FROM yourTableName
WHERE yourColumnName = (SELECT MAX(yourColumnName) FROM yourTableName) ;

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

mysql> create table FirstAndLastDataDemo
   -> (
   -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> EmployeeName varchar(20),
   -> EmployeeAge int
   -> );
Query OK, 0 rows affected (0.59 sec)

Example

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

mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('John',23);
Query OK, 1 row affected (0.15 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('Bob',13);
Query OK, 1 row affected (0.11 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('Larry',24);
Query OK, 1 row affected (0.14 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('Sam',14);
Query OK, 1 row affected (0.18 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('Mike',31);
Query OK, 1 row affected (0.10 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('James',18);
Query OK, 1 row affected (0.16 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge)
values('Maxwell',28);
Query OK, 1 row affected (0.17 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('David',27);
Query OK, 1 row affected (0.12 sec)
mysql> insert into FirstAndLastDataDemo(EmployeeName,EmployeeAge) values('Chris',22);
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from FirstAndLastDataDemo;

Output

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
|          1 | John         |          23 |
|          2 | Bob          |          13 |
|          3 | Larry        |          24 |
|          4 | Sam          |          14 |
|          5 | Mike         |          31 |
|          6 | James        |          18 |
|          7 | Maxwell      |          28 |
|          8 | David        |          27 |
|          9 | Chris        |          22 |
+------------+--------------+-------------+
9 rows in set (0.00 sec)

Here is the query to get first row data and last row data −

mysql> SELECT *
-> FROM FirstAndLastDataDemo
-> WHERE EmployeeId = (SELECT MIN(EmployeeId) FROM FirstAndLastDataDemo)
-> UNION
-> SELECT *
-> FROM FirstAndLastDataDemo
-> WHERE EmployeeId = (SELECT MAX(EmployeeId) FROM FirstAndLastDataDemo);

Output

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
|          1 |         John |          23 |
|          9 |        Chris |          22 |
+------------+--------------+-------------+
2 rows in set (0.00 sec)

Samual Sam

Published on 19-Mar-2019 08:38:19

• Related Questions & Answers
• How to select first and last row record using LIMIT in MySQL?
• How to select last row in MySQL?
• How to select the first and last row based on group column in an R data frame?
• Get total in the last row of MySQL result?
• How to select last 10 rows from MySQL?
• How to select first 10 elements from a MySQL database?
• MySQL query to get result from multiple select statements?
• Get all rows apart from first and last in MySQL
• Select and add result of multiplying two columns from a table in MySQL?
• How to extract the last row from list of a data frame in R?
• MySQL query to select rows except first row in descending order?
• MySQL LIMIT to select a single row
• MySQL query to select one specific row and another random row?
• Select a random row in MySQL
• How to get first and last elements from ArrayList in Java?