Node.js MySQL ORDER BY

Node.js MySQL ORDER BY

Node.js MySQL ORDER BY is used with a SELECT query to return rows in a predictable sorted order. The sorting is done by MySQL, and the Node.js program receives the result set after MySQL has arranged the rows by the specified column or columns.

In this tutorial, we will learn how to sort rows from a MySQL table in ascending order, descending order, alphabetical order, and by more than one column using Node.js. The examples use the mysql package and a sample students table.

By default, MySQL sorts ORDER BY results in ascending order. You can write ASC explicitly for ascending order, or use DESC for descending order. If you do not use ORDER BY, you should not depend on the natural order of rows returned by the database.

• Node.js MySQL ORDER BY syntax for ASC and DESC
• Example to ORDER records in ascending order by a numeric column
• Example to ORDER records alphabetically by a text column
• Example to ORDER records in descending order
• Node.js MySQL ORDER BY multiple columns
• Safe dynamic ORDER BY columns in Node.js MySQL

Node.js MySQL ORDER BY Syntax for ASC and DESC

The ORDER BY clause is written after the table name and optional filtering clauses in a SELECT statement. The basic MySQL syntax is shown below.

SELECT column1, column2
FROM table_name
ORDER BY column_name ASC;

SELECT column1, column2
FROM table_name
ORDER BY column_name DESC;

In Node.js, the SQL statement is passed to con.query(). For a fixed sort column, you can write the ORDER BY clause directly in the SQL string.

con.query("SELECT * FROM students ORDER BY marks ASC", function (err, result) {
  if (err) throw err;
  console.log(result);
});

The examples below use a table named students with columns such as name, rollno, and marks. Sorting by marks demonstrates numeric sorting, and sorting by name demonstrates alphabetical sorting.

Column	Meaning in examples	ORDER BY behavior
marks	Numeric score of the student	Sorts from smaller to larger with ASC, and larger to smaller with DESC
name	Student name	Sorts alphabetically according to the MySQL collation used by the column
rollno	Student roll number	Useful as a tie-breaker when two rows have the same marks or name

Example 1 – ORDER Rows from MySQL Table in Ascending Order – Node.js

In this example, we will get rows from MySQL table sorted in ascending order.

example.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students ORDER BY marks", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Run the above Node.js MySQL ORDER BY example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.js 
[ RowDataPacket { name: 'Ross', rollno: 7, marks: 54 },
  RowDataPacket { name: 'John', rollno: 1, marks: 74 },
  RowDataPacket { name: 'Arjun', rollno: 2, marks: 74 },
  RowDataPacket { name: 'Prasanth', rollno: 3, marks: 77 },
  RowDataPacket { name: 'Adarsh', rollno: 4, marks: 78 },
  RowDataPacket { name: 'Sai', rollno: 6, marks: 84 },
  RowDataPacket { name: 'Monica Gellar', rollno: 8, marks: 86 },
  RowDataPacket { name: 'Bruce Wane', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 } ]

The records are sorted in ascending order with respect to marks column. Since ASC is the default direction, ORDER BY marks and ORDER BY marks ASC give the same sorting direction.

In the output, John and Arjun both have marks value 74. When duplicate sort values are possible, add another column to ORDER BY if you need a stable tie-breaker.

SELECT * FROM students
ORDER BY marks ASC, rollno ASC;

Example 2 – ORDER Rows from MySQL Table Alphabetically by Name

In this example, we will sort rows based on a column of type TEXT.

example.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students ORDER BY name", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Run the above Node.js MySQL ORDER BY example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node example.js 
[ RowDataPacket { name: 'Adarsh', rollno: 4, marks: 78 },
  RowDataPacket { name: 'Arjun', rollno: 2, marks: 74 },
  RowDataPacket { name: 'Bruce Wane', rollno: 10, marks: 92 },
  RowDataPacket { name: 'John', rollno: 1, marks: 74 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'Monica Gellar', rollno: 8, marks: 86 },
  RowDataPacket { name: 'Prasanth', rollno: 3, marks: 77 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Ross', rollno: 7, marks: 54 },
  RowDataPacket { name: 'Sai', rollno: 6, marks: 84 },
  RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 } ]

The records are sorted in ascending order with respect to name column. For text columns, alphabetical order depends on the collation configured for the column or query. In most beginner examples, this means names starting with A appear before names starting with B, C, and so on.

Example 3 – ORDER Rows from MySQL Table in Descending Name Order

In this example, we will sort rows in descending order of a specific column “name”.

example.js

// include mysql module
var mysql = require('mysql');

// create a connection variable with the required details
var con = mysql.createConnection({
  host: "localhost",	// ip address of server running mysql
  user: "arjun",	// user name to your mysql database
  password: "password",	// corresponding password
  database: "studentsDB" // use the specified database
});

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;
  // if connection is successful
  con.query("SELECT * FROM students ORDER BY name DESC", function (err, result, fields) {
	// if any error while executing above query, throw error
    if (err) throw err;
	// if there is no error, you have the result
    console.log(result);
  });
});

Run the above Node.js MySQL ORDER BY example program.

Output

arjun@arjun-VPCEH26EN:~/workspace/nodejs$ node DescOrderExample.js 
[ RowDataPacket { name: 'Sukumar', rollno: 11, marks: 99 },
  RowDataPacket { name: 'Sai', rollno: 6, marks: 84 },
  RowDataPacket { name: 'Ross', rollno: 7, marks: 54 },
  RowDataPacket { name: 'Raja', rollno: 5, marks: 94 },
  RowDataPacket { name: 'Prasanth', rollno: 3, marks: 77 },
  RowDataPacket { name: 'Monica Gellar', rollno: 8, marks: 86 },
  RowDataPacket { name: 'Lee', rollno: 9, marks: 98 },
  RowDataPacket { name: 'John', rollno: 1, marks: 74 },
  RowDataPacket { name: 'Bruce Wane', rollno: 10, marks: 92 },
  RowDataPacket { name: 'Arjun', rollno: 2, marks: 74 },
  RowDataPacket { name: 'Adarsh', rollno: 4, marks: 78 } ]

The records are sorted in descending order with respect to name column. In a descending alphabetical sort, values closer to Z appear before values closer to A.

Node.js MySQL ORDER BY Multiple Columns for Tie-Breaking

You can sort by more than one column by separating the columns with commas. MySQL first sorts by the first column. If two rows have the same value in that column, MySQL then compares the next column.

SELECT * FROM students
ORDER BY marks DESC, name ASC;

The query above sorts students from highest marks to lowest marks. If two students have the same marks, their names are sorted alphabetically. The same SQL can be used inside Node.js as shown below.

con.query("SELECT * FROM students ORDER BY marks DESC, name ASC", function (err, result) {
  if (err) throw err;
  console.log(result);
});

You may also see queries such as ORDER BY 1 or ORDER BY 1, 2. In MySQL, the number refers to the position of the selected column expression. For example, ORDER BY 1 sorts by the first expression in the SELECT list.

SELECT name, marks
FROM students
ORDER BY 2 DESC, 1 ASC;

The query above sorts by marks first because marks is the second selected expression, and then by name. For application code, explicit column names are usually easier to read and safer to maintain than column positions.

ORDER BY Placement with WHERE, GROUP BY, and LIMIT in Node.js MySQL Queries

When a SELECT query has multiple clauses, ORDER BY is written after WHERE, GROUP BY, and HAVING, and before LIMIT. This order matters because MySQL must first choose or group the rows before sorting the final result set.

SELECT column_list
FROM table_name
WHERE condition
GROUP BY grouped_column
HAVING group_condition
ORDER BY sort_column ASC
LIMIT row_count;

For a simple list page in a Node.js app, a common pattern is to filter rows, sort them, and then limit how many rows are returned.

con.query(
  "SELECT * FROM students WHERE marks >= ? ORDER BY marks DESC LIMIT ?",
  [70, 5],
  function (err, result) {
    if (err) throw err;
    console.log(result);
  }
);

The placeholders in this example are for values such as 70 and 5. Do not use value placeholders as a direct substitute for user-selected column names. Column names and sort directions need separate handling.

Safe Dynamic ORDER BY Columns in Node.js MySQL Routes

Many Node.js applications allow users to choose a sort column, such as sorting a student list by name, marks, or roll number. Do not directly concatenate an unchecked query string value into ORDER BY. A safe approach is to allow only known column names and known sort directions.

var allowedSortColumns = {
  name: "name",
  marks: "marks",
  rollno: "rollno"
};

var requestedColumn = req.query.sort || "marks";
var requestedDirection = String(req.query.direction || "ASC").toUpperCase();

var sortColumn = allowedSortColumns[requestedColumn] || "marks";
var sortDirection = requestedDirection === "DESC" ? "DESC" : "ASC";

var sql = "SELECT * FROM students ORDER BY " +
  mysql.escapeId(sortColumn) + " " + sortDirection;

con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(result);
});

In the example above, sort can only become one of the column names in allowedSortColumns. The direction can only become ASC or DESC. The mysql.escapeId() function is used for the SQL identifier. For more details, see the mysqljs documentation on escaping query identifiers.

Node.js MySQL ORDER BY Performance Notes for Sorted Result Sets

ORDER BY is simple to write, but large tables can require extra sorting work. For frequently used list pages, check whether the sorted column is indexed and review the query plan with EXPLAIN. MySQL may be able to use an index for some ORDER BY queries, but it depends on the selected columns, filters, sort directions, and indexes available.

For official SQL behavior, refer to the MySQL manual section on sorting rows. For application code, also test your Node.js query with real data volume, not only with a small sample table.

Node.js MySQL ORDER BY FAQs

How do you ORDER BY in MySQL from Node.js?

Write the ORDER BY clause in the SELECT statement that you pass to con.query(). For example, SELECT * FROM students ORDER BY marks DESC returns rows sorted by marks from highest to lowest.

How do you sort MySQL rows alphabetically in a Node.js query?

Use ORDER BY on the text column. For example, SELECT * FROM students ORDER BY name ASC sorts the rows by the name column in ascending alphabetical order according to the column collation.

Which comes first in MySQL: GROUP BY or ORDER BY?

GROUP BY comes before ORDER BY. A typical clause order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and then LIMIT.

What does ORDER BY 1, 2, or 3 mean in MySQL?

ORDER BY 1 means sort by the first expression in the SELECT list. ORDER BY 2 means sort by the second expression, and so on. This works in MySQL, but using column names is usually clearer in Node.js application code.

Can I use a placeholder for an ORDER BY column in Node.js MySQL?

Use placeholders for values, not as a direct replacement for unchecked column names. For dynamic sorting, choose the column from a whitelist and escape the identifier with mysql.escapeId(). Also validate the direction so that it can only be ASC or DESC.

Node.js MySQL ORDER BY Editorial QA Checklist

• Confirm that every Node.js query that needs predictable row order includes an ORDER BY clause.
• Check that numeric examples sort by numeric columns such as marks, and alphabetical examples sort by text columns such as name.
• When duplicate sort values are possible, add a second ORDER BY column such as rollno for a stable tie-breaker.
• For user-selected sorting, verify that column names come from a whitelist and that sort direction is limited to ASC or DESC.
• For larger tables, review whether indexes support the most common ORDER BY queries and test the query plan with realistic data.

Node.js MySQL ORDER BY Summary

In this Node.js Tutorial – Node.js MySQL module – we learned how to use Node.js MySQL ORDER BY to sort records in ascending or descending order with respect to a column. We also covered alphabetical sorting, multiple-column sorting, ORDER BY clause placement, and safer handling of dynamic sort options in Node.js applications.