Syntax error Get MAX and MIN values along with their row id in MySQL?

Get MAX and MIN values along with their row id in MySQL?

You can use aggregate function MAX() and MIN() for this.

Let us first create a table −

mysql> create table DemoTable
   (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Number1 int,
   Number2 int
   );
Query OK, 0 rows affected (0.89 sec)

Insert records in the table using insert command −

mysql> insert into DemoTable(Number1,Number2) values(67,45);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(Number1,Number2) values(90,40);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(Number1,Number2) values(80,43);
Query OK, 1 row affected (0.48 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable ;

This will produce the following output −

+----+---------+---------+
| Id | Number1 | Number2 |
+----+---------+---------+
| 1  | 67      | 45      |
| 2  | 90      | 40      |
| 3  | 80      | 43      |
+----+---------+---------+
3 rows in set (0.00 sec)

Following is the query to get MAX and MIN values along with their row id −

mysql> select * from DemoTable
where Number1 = (select max(Number1) from DemoTable)
or Number2 = (select min(Number2) from DemoTable);

This will produce the following output −

+----+---------+---------+
| Id | Number1 | Number2 |
+----+---------+---------+
| 2  | 90      | 40      |
+----+---------+---------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:26+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements