Node.js MySQL Order By

The ORDER BY clause in MySQL is used to sort the result set of a query in either ascending or descending order. It is often used with the SELECT statement to organize the data according to one or more columns.

 

Key Features of Node.js MySQL Order By

1. Sorting Results: Use ORDER BY to sort the results of a query.
2. Ascending and Descending: Sort results in ascending (ASC) or descending (DESC) order.
3. Multiple Columns: Sort by multiple columns for more complex ordering.
4. Default Sorting: By default, sorting is in ascending order if no order is specified.

 

Step 1 Prerequisites

Ensure that MySQL is installed and the mysql2 package is added to your Node.js project.

npm install mysql2

Step 2 Basic Query with ORDER BY Clause

You can sort the records based on a single column in either ascending or descending order.

Example Code for Ascending Order

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select all records and order them by age in ascending order
  const selectQuery = 'SELECT * FROM users ORDER BY age ASC';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Sorted records (ascending):', results);
  });

  // Close the connection
  connection.end();
});

Output:

Sorted records (ascending): [
  { id: 1, name: 'John Doe', email: '[email protected]', age: 25 },
  { id: 2, name: 'Jane Doe', email: '[email protected]', age: 30 },
  { id: 3, name: 'Sam Smith', email: '[email protected]', age: 35 }
]

Example Code for Descending Order

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select all records and order them by age in descending order
  const selectQuery = 'SELECT * FROM users ORDER BY age DESC';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Sorted records (descending):', results);
  });

  // Close the connection
  connection.end();
});

Output:

Sorted records (descending): [
  { id: 3, name: 'Sam Smith', email: '[email protected]', age: 35 },
  { id: 2, name: 'Jane Doe', email: '[email protected]', age: 30 },
  { id: 1, name: 'John Doe', email: '[email protected]', age: 25 }
]

Step 3 Sorting by Multiple Columns

You can sort the results by more than one column by specifying multiple columns in the ORDER BY clause. This is useful when you need to sort data by multiple attributes.

Example Code for Sorting by Multiple Columns

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select all records and order by age (ascending) and name (alphabetical order)
  const selectQuery = 'SELECT * FROM users ORDER BY age ASC, name ASC';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Sorted records by multiple columns:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Sorted records by multiple columns: [
  { id: 1, name: 'John Doe', email: '[email protected]', age: 25 },
  { id: 2, name: 'Jane Doe', email: '[email protected]', age: 30 },
  { id: 3, name: 'Sam Smith', email: '[email protected]', age: 35 }
]

Step 4 Using Aliases for Columns in ORDER BY

If you want to make your query more readable, you can use column aliases and refer to these aliases in the ORDER BY clause.

Example Code with Aliases

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select all records and order by alias `userAge`
  const selectQuery = 'SELECT name, age AS userAge FROM users ORDER BY userAge DESC';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Sorted records using alias:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Sorted records using alias: [
  { name: 'Sam Smith', userAge: 35 },
  { name: 'Jane Doe', userAge: 30 },
  { name: 'John Doe', userAge: 25 }
]

Step 5 Limiting Results with ORDER BY

You can also use the LIMIT clause along with ORDER BY to return a specific number of results, sorted in the order you need.

Example Code with LIMIT

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Select top 2 records ordered by age in ascending order
  const selectQuery = 'SELECT * FROM users ORDER BY age ASC LIMIT 2';

  connection.query(selectQuery, (err, results) => {
    if (err) {
      console.error('Error fetching records:', err.stack);
      return;
    }
    console.log('Limited sorted records:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Limited sorted records: [
  { id: 1, name: 'John Doe', email: '[email protected]', age: 25 },
  { id: 2, name: 'Jane Doe', email: '[email protected]', age: 30 }
]

 

Summary

The ORDER BY clause in MySQL allows you to sort query results based on one or more columns in ascending or descending order. You can combine multiple columns, use aliases, and even limit the number of results. This helps in organizing and presenting the data as per your requirements.