MySQL Order By

The ORDER BY clause in MySQL is used to sort the result set of a query. By default, MySQL sorts data in ascending order, but you can also sort it in descending order based on one or more columns.

Why Use ORDER BY in MySQL?

1. Organize Results: Display data in a structured and readable format.
2. Custom Sorting: Control the order of your query results based on your requirements.
3. Dynamic Applications: Provide sorted data for features like dashboards, reports, and search results.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running on your system.

Node.js Installed: Install Node.js if you haven’t already.

MySQL Package Installed: Install the MySQL package:

npm install mysql

Setting Up the Node.js Project

Initialize the Project

npm init -y

Install MySQL Module:

npm install mysql

Create a File: Create a file named app.js.

Connecting to the MySQL Database

Before using the ORDER BY clause, you need to connect to your MySQL database.

Example: Connect to MySQL

const mysql = require('mysql');

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

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

Using ORDER BY in Node.js

The ORDER BY clause can be used with the SELECT statement to sort data.

Example 1: Sort Data in Ascending Order

By default, the ORDER BY clause sorts data in ascending order.

const sql = 'SELECT * FROM users ORDER BY age ASC';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users sorted by age (ascending):', results);
});

// Close the connection
connection.end();

Explanation:

• ORDER BY age ASC: Sorts the users table by the age column in ascending order.
• ASC: This keyword explicitly specifies ascending order.

Example 2: Sort Data in Descending Order

To sort data in descending order, use the DESC keyword.

const sql = 'SELECT * FROM users ORDER BY age DESC';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users sorted by age (descending):', results);
});

// Close the connection
connection.end();

Explanation:

• DESC: Sorts the age column in descending order.

Sorting by Multiple Columns

You can sort data by more than one column. If the first column values are identical, MySQL uses the second column to sort.

Example 3: Sort by Multiple Columns

const sql = 'SELECT * FROM users ORDER BY age ASC, name ASC';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users sorted by age and name:', results);
});

// Close the connection
connection.end();

Explanation:

• age ASC: Sorts by age in ascending order.
• name ASC: If two rows have the same age, they are sorted by name in ascending order.

Combining ORDER BY with WHERE Clause

You can combine ORDER BY with the WHERE clause to filter and sort data simultaneously.

Example 4: Filter and Sort Data

const sql = 'SELECT * FROM users WHERE age > ? ORDER BY name ASC';
const minAge = 25;

connection.query(sql, [minAge], (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users older than 25, sorted by name:', results);
});

// Close the connection
connection.end();

Explanation:

• WHERE age > ?: Filters users older than 25.
• ORDER BY name ASC: Sorts the filtered results by name in ascending order.

Limiting Sorted Results

The LIMIT clause can be combined with ORDER BY to fetch a specific number of sorted rows.

Example 5: Fetch Top 5 Records

const sql = 'SELECT * FROM users ORDER BY age DESC LIMIT 5';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Top 5 oldest users:', results);
});

// Close the connection
connection.end();

Explanation:

• ORDER BY age DESC: Sorts users by age in descending order.
• LIMIT 5: Retrieves only the first 5 rows from the sorted data.

Real-World Use Case

Imagine building a leaderboard for a gaming application. You can use the ORDER BY clause to display players ranked by their scores.

Example: Fetch Top Players

const sql = 'SELECT name, score FROM players ORDER BY score DESC LIMIT 10';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching leaderboard:', err);
        return;
    }
    console.log('Top 10 players:', results);
});

// Close the connection
connection.end();