MySQL Order By

MySQL Order By: Sorting Query Result Sets in Node.js

Sorting record sets returned by MySQL in Node.js applications is controlled using the ORDER BY clause, allowing sorting by single or multiple columns in ascending (ASC) or descending (DESC) direction.

Query Ordering Architecture

flowchart LR
    A["Raw Unsorted Database Table"] --> B["ORDER BY created_at DESC, name ASC"]
    B --> C["Sorted ResultSet Returned to Node.js"]

Practical Code Example

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'rootpassword',
    database: 'kodersolution_dev'
});

async function fetchSortedUsers(sortDirection = 'DESC') {
    try {
        // Validate sort direction dynamically to prevent injection
        const safeDirection = sortDirection.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

        const sql = `
            SELECT id, name, email, created_at 
            FROM users 
            ORDER BY created_at ${safeDirection}, name ASC
        `;

        const [rows] = await pool.execute(sql);
        
        console.log(`--- Users Sorted by Creation Date (${safeDirection}) ---`);
        rows.forEach(u => console.log(`[${u.created_at.toISOString()}] ${u.name}`));
    } catch (error) {
        console.error('MySQL ORDER BY Error:', error.message);
    } finally {
        await pool.end();
    }
}

fetchSortedUsers('DESC');

Best Practices & Gotchas

• Validate Dynamic Order Directions: SQL column identifiers and directions (ASC/DESC) cannot be parameterized with ?; validate them against explicit allowlists in code.
• Index Order Columns: Create composite indexes on columns used in ORDER BY clauses to eliminate slow Using filesort operations in MySQL query plans.
• Combine with LIMIT: Always combine ORDER BY with LIMIT when fetching top records (e.g. latest 10 posts).

Self-Check Challenge

Why can you not use placeholder ? parameters for ORDER BY ? column names in SQL prepared statements?