Node.js MySQL ORDER BY

In this post, we will discuss how to return the rows from an MYSQL table in an order in XAMPP Server using Node.js Script.

It is important to install mysql package in node.js

Command to install the mysql package:

npm install mysql
				

ORDER BY Clause:

ORDER BY Clause in MySQL is used to return rows returned by the SELECT Clause in Ascending or Descending order base don the column specified.

1. ASC will return the rows in Ascending order
2. DESC will return the rows in Descending order.

By default It will return in Ascending order.

Steps:

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 to order the rows based on particular column. It will take two parameters. The first parameter is the SQL Query and the second parameter will handle the result
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT column/s FROM table_name 
ORDER BY column ASC", 
function (error, result) {
 
    console.log(result);
  });
});

Copied

connection_data.connect(function(error) {
connection_data.query("SELECT column/s FROM table_name 
ORDER BY column DESC", 
function (error, result) {
 
    console.log(result);
  });
});

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

It will return the records one by one in the following format:

[
  RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},

 RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},
.....
.....

Here, each RowDataPacket represent a row.

Consider the village table with the following records:

ORDER BY Example 1:-

Let's select all the records from village table in Ascending order by people and village_name column separately.

// 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 select all rows in ascending order by people column.
  connection_data.query("SELECT * FROM village ORDER BY people ASC", 
  function (error, result) {
 
 console.log("ORDER BY people Column in Ascending Order:");
 
//Display the records one by one
    console.log(result);
  });
  
  // Write SQL query to select all rows in ascending order by village_name column.
  connection_data.query("SELECT * FROM village ORDER BY people ASC", 
  function (error, result) {
 
 console.log("ORDER BY village_name Column in Ascending Order:");
 
//Display the records one by one
    console.log(result);
  });
});

Output:

ORDER BY people Column in Ascending Order:
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  }
]
ORDER BY village_name Column in Ascending Order:
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  }
]

So we can see that only rows were returned in ascending order by these two columns.

ORDER BY Example 2:-

Let's select all the records from village table in Descending order by people and village_name column separately.

// 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 select all rows in descending order by people column.
  connection_data.query("SELECT * FROM village ORDER BY people DESC",
function (error, result) {
 
 console.log("ORDER BY people Column in Descending Order:");
 
//Display the records one by one
    console.log(result);
  });
  
  // Write SQL query to select all rows in descending order by village_name column.
  connection_data.query("SELECT * FROM village ORDER BY people DESC", 
  function (error, result) {
 
 console.log("ORDER BY village_name Column in Descending Order:");
 
//Display the records one by one
    console.log(result);
  });
});

Output:

ORDER BY people Column in Descending Order:
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  }
]
ORDER BY village_name Column in Descending Order:
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  }
]

Let's check in our XAMPP Server whether the rows deleted or not.

Summary

So we seen how to use ORDER BY on a MySQL Table in XAMPP Server using Node.js. If we didn't specify ASC or DESC after ORDER BY Clause, then it will sort the rows in Ascending order.