Node.js MySQL - GREATEST()

In this post, we will discuss how to return the maximum value from multiple column values in a MySQL XAMPP server through Node.js application with GREATEST() function.

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

Command to install the mysql package:

npm install mysql
				

GREATEST() Function:

GREATEST() is used to return the maximum value from multiple values. It takes columns as a parameter. Minimum two columns are required.

Syntax:

GREATEST(column1,column2,....columnn)

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 GREATEST() function
Copied

connection_data.connect(function(error) {
  connection_data.query("SELECT GREATEST(column1,column2,...),.... 
  FROM table_name WHERE condition/s...", 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 details table with the following records:

GREATEST Example 1:-

Let's return maximum values among 4 columns - price,rate,other1 and other2.

// 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 that uses GREATEST() function
  connection_data.query("SELECT price,rate,other1,other2,
  GREATEST(price,rate,other1,other2)  from details", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    price: 12,
    rate: 34,
    other1: 7,
    other2: 67.89,
    'GREATEST(price,rate,other1,other2)': 67.88999938964844
  },
  RowDataPacket {
    price: 34,
    rate: 34,
    other1: 2,
    other2: 0.9,
    'GREATEST(price,rate,other1,other2)': 34
  },
  RowDataPacket {
    price: 23,
    rate: 34,
    other1: 6,
    other2: 0,
    'GREATEST(price,rate,other1,other2)': 34
  },
  RowDataPacket {
    price: 45,
    rate: 34,
    other1: 37,
    other2: 67.9,
    'GREATEST(price,rate,other1,other2)': 67.9000015258789
  }
]

Maximum values among 4 columns were returned for all 4 rows.

GREATEST Example 2:-

Let's return maximum values among 2 columns - other1 and price where name is facility1.

// 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 that uses GREATEST() function
  connection_data.query("SELECT price,other1,GREATEST(price,other1) 
  from details where name='facility1'", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { price: 12, other1: 7, 'GREATEST(price,other1)': 12 }
]

There is only one facility1 and the maximum value among price and other1 is price(12).

Summary

In this post, we seen how to use GREATEST() function on MySQL table in XAMPP Server and also it can be possible to specify WHERE clause along this function.