Node.js - MySQL Order By

---

---

In a Node.js application, you may want to retrieve data from a MySQL database in ascending or descending order. In MySQL, the ORDER BY clause sorts the resultset returned by the SELECT statement in a specified order. When using a Node.js application to retrieve data from a MySQL database, the query string argument of the query() method of the connection object as defined in the mysql module should have the ORDER BY clause. In this chapter, the various use cases of ORDER BY clause in SELECT statement in a Node.js application have been described with examples.

The syntax of ORDER BY clause in SELECT statement is as follows −

SELECT select_list FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

You specify the one or more columns that you want to sort after the ORDER BY clause. The default sort order is ascending (ASC).

ORDER BY column1; 

To obtain the rows in a descending order, use DESC in front of the column name.

ORDER BY column1 DESC;

The ascending order on numeric fields obtains the rows from smallest to largest value of the field. Whereas, the ascending order on a string field (such as VARCHAR or TEXT) results in alphabetical order from a to z. Similarly, for a DateTime field, the ascending order refers to the chronological order, i.e., from earlier date to the latter date.

Example

The following Node.js code, the query() method of mysql module returns the rows from employee table in the ascending order of salary field.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT * FROM employee ORDER BY salary;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

Output

[
  RowDataPacket { id: 1, name: 'Ravi', age: 25, salary: 25000 },
  RowDataPacket { id: 3, name: 'Meena', age: 26, salary: 27000 },
  RowDataPacket { id: 2, name: 'Anil', age: 26, salary: 30000 }
]

As an example of descending order with DESC keyword with a field, change the query string to the following −

var qry =`SELECT * FROM employee ORDER BY name DESC;`;

The resultant result set will be in the descending order of names in employee able.

[
  RowDataPacket { id: 1, name: 'Ravi', age: 25, salary: 25000 },
  RowDataPacket { id: 3, name: 'Meena', age: 26, salary: 27000 },
  RowDataPacket { id: 2, name: 'Anil', age: 26, salary: 30000 }
]

Multiple-column sort

As mentioned earlier, you can specify the one or more columns after the ORDER BY clause. This first sorts the rows on the first column. The rows with same value of first column are sorted on the value of second column. In effect, this becomes a sort within a sort or nested sort.

To illustrate multiple-column sort, we shall use the world database that is pre-installed in the MySQL 8.0 installation. The world database includes a city table with the following structure −

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

With the following Node.js code, we shall fetch District-wise list of cities sorted in ascending order of population.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "world"
});

var qry =`select * from city where name like 'D%' and countrycode='IND' order by district, population;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
  
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

Output

[
  RowDataPacket {
    ID: 1276,
    Name: 'Dibrugarh',
    CountryCode: 'IND',
District: 'Assam',
    Population: 120127
  },
  RowDataPacket {
    ID: 1350,
    Name: 'Dehri',
    CountryCode: 'IND',
    District: 'Bihar',
    Population: 94526
  },
  RowDataPacket {
    ID: 1138,
    Name: 'Darbhanga',
    CountryCode: 'IND',
    District: 'Bihar',
    Population: 218391
  },
  RowDataPacket {
    ID: 1206,
    Name: 'Durg',
    CountryCode: 'IND',
    District: 'Chhatisgarh',
    Population: 150645
  },
  RowDataPacket {
    ID: 1351,
    Name: 'Delhi Cantonment',
    CountryCode: 'IND',
    District: 'Delhi',
    Population: 94326
  },
  RowDataPacket {
    ID: 1025,
    Name: 'Delhi',
    CountryCode: 'IND',
    District: 'Delhi',
    Population: 7206704
  },
  RowDataPacket {
    ID: 1203,
    Name: 'Dhanbad',
    CountryCode: 'IND',
    District: 'Jharkhand',
    Population: 151789
  },
  RowDataPacket {
    ID: 1119,
    Name: 'Davangere',
    CountryCode: 'IND',
    District: 'Karnataka',
    Population: 266082
  },
  RowDataPacket {
    ID: 1347,
    Name: 'Damoh',
    CountryCode: 'IND',
    District: 'Madhya Pradesh',
    Population: 95661
  },
  RowDataPacket {
    ID: 1186,
    Name: 'Dewas',
    CountryCode: 'IND',
    District: 'Madhya Pradesh',
    Population: 164364
  },
  RowDataPacket {
    ID: 1113,
    Name: 'Dhule (Dhulia)',
    CountryCode: 'IND',
    District: 'Maharashtra',
    Population: 278317
  },
  RowDataPacket {
    ID: 1167,
    Name: 'Dindigul',
    CountryCode: 'IND',
    District: 'Tamil Nadu',
    Population: 182477
  },
  RowDataPacket {
    ID: 1117,
    Name: 'Dehra Dun',
    CountryCode: 'IND',
    District: 'Uttaranchal',
    Population: 270159
  },
  RowDataPacket {
    ID: 1214,
    Name: 'Dabgram',
    CountryCode: 'IND',
    District: 'West Bengal',
    Population: 147217
  },
  RowDataPacket {
    ID: 1082,
    Name: 'Durgapur',
    CountryCode: 'IND',
    District: 'West Bengal',
    Population: 425836
  }
]