Node.js MySQL MAX()

In this post, we will discuss how to perform MAX aggregate function on MySQL table in XAMPP server using Node.js.

It is important to install mysql package in node.js.

Command to install the mysql package:

npm install mysql
				

MAX() Function:

MAX is an aggregate function in sql which is used to return the maximum value in a column. It can also be used with GROUP BY to return the maximum value in each group.

Steps for Node.js script:

Now let's see steps

1. First start your XAMPP Server (Both Apache and MySQL).
2. Open Notepad or any text-editor and write the Node.js script
3. In that script, first we have to load the mysql package using the below syntax
var mysql_package = require('mysql');
4. Create the connection using the server,username and password.
Copied

var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"database_name"
});

5. Write the sql query that uses MAX aggregate function.
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT MAX(column1), MAX(column2),... 
from table_name)", function (error, result) {
    console.log(result);
  });
});

6. Now type the following command in your command prompt to run the script.
node file_name.js

Consider the village table with the following records:

Example 1:- MAX()

Node.js Script to return maximum value in area column.

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to return maximum value from the area column on field table.
  connection_data.query("SELECT MAX(area) FROM field", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { 'MAX(area)': 890 } ]

So, the maximum value present in the area column is 20.

Example 2:- MAX() with GROUP BY()

Node.js Script

1. To group all values in district column and return maximum area in each group.
2. To group all values in field_type column and return maximum area in each group.

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to return maximum value in area 
//column by group on district column.
  connection_data.query("SELECT district,MAX(area) FROM 
  field GROUP BY district", function (error, result) {
	  
  console.log("GROUP BY DISTRICT");
//Display the records one by one
    console.log(result);
  });
  
  // Write SQL query to return maximum value in area 
  //column by group on field_type column.
  connection_data.query("SELECT field_type,MAX(area) FROM 
  field GROUP BY field_type", function (error, result) {
	  
  console.log("GROUP BY FIELD TYPE");
//Display the records one by one
    console.log(result);
  });
});

Output:

GROUP BY DISTRICT
[
  RowDataPacket { district: 'guntur', 'MAX(area)': 220 },
  RowDataPacket { district: 'kadapa', 'MAX(area)': 890 },
  RowDataPacket { district: 'nellore', 'MAX(area)': 100 },
  RowDataPacket { district: 'ongole', 'MAX(area)': 780 },
  RowDataPacket { district: 'visakha', 'MAX(area)': 890 }
]
GROUP BY FIELD TYPE
[
  RowDataPacket { field_type: 'black', 'MAX(area)': 120 },
  RowDataPacket { field_type: 'red', 'MAX(area)': 780 },
  RowDataPacket { field_type: 'sand', 'MAX(area)': 890 }
]

1. The values are grouped based on the values in district column and corresponding maximum area is returned for each group.
2. The values are grouped based on the values in field_type column and corresponding maximum area is returned for each group.

Example 3:- MAX() with WHERE Clause

Node.js Script to return maximum area with field_type black.

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to return maximum value in area column 
//where field_type is black.
  connection_data.query("SELECT district,field_type,MAX(area) 
  FROM field where field_type='black' ", function (error, result) {
	  
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    district: 'guntur',
    field_type: 'black',
    'MAX(area)': 120
  }
]

So we can see that maximum value is 120 among field_type black group.

Summary

In this post, we seen how to return maximum value in a column and how to apply GROUP BY along with MAX aggregate function. You can try in your machine with the help of above examples.