MySQL ORDER BY

Sort database results in ascending or descending order

📊 What is ORDER BY?

ORDER BY sorts query results based on one or more columns. You can arrange data in ascending (A-Z, 0-9) or descending (Z-A, 9-0) order for better organization and presentation. 


<?php
// Sort results by name
$sql = "SELECT * FROM users ORDER BY name ASC";
$result = $conn->query($sql);
?>

Key Concepts

⬆️

Ascending

Sort from lowest to highest 

// A to Z, 0 to 9
ORDER BY name ASC
⬇️

Descending

Sort from highest to lowest 

// Z to A, 9 to 0
ORDER BY age DESC
🔢

Multiple Columns

Sort by multiple fields 

// Primary, then secondary
ORDER BY city, name
🎯

Mixed Order

Different order per column 

// Combine ASC and DESC
ORDER BY age DESC, name ASC

🔹 ORDER BY Ascending (ASC)

Sort results in ascending order from lowest to highest. ASC is the default sorting order, so you can omit it if desired. 

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Sort by name in ascending order (A to Z)
$sql = "SELECT name, email, age FROM users ORDER BY name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Users sorted by name (A-Z):</h3>";
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . " - " . $row["email"] . " - Age: " . $row["age"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Users sorted by name (A-Z):

Alice Brown - [email protected] - Age: 28

Bob Johnson - [email protected] - Age: 35

Charlie Davis - [email protected] - Age: 22

John Doe - [email protected] - Age: 30

🔹 ORDER BY Descending (DESC)

Sort results in descending order from highest to lowest. This is useful for showing newest items first or highest values at the top. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Sort by age in descending order (highest to lowest)
$sql = "SELECT name, age FROM users ORDER BY age DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Users sorted by age (oldest first):</h3>";
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . " - Age: " . $row["age"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Users sorted by age (oldest first):

Bob Johnson - Age: 35

John Doe - Age: 30

Alice Brown - Age: 28

Charlie Davis - Age: 22

🔹 ORDER BY Multiple Columns

Sort by multiple columns to create hierarchical ordering. The first column is the primary sort, and subsequent columns break ties. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Sort by city first, then by name within each city
$sql = "SELECT name, city, age FROM users ORDER BY city ASC, name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Users sorted by city, then name:</h3>";
    while($row = $result->fetch_assoc()) {
        echo $row["city"] . " - " . $row["name"] . " - Age: " . $row["age"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Users sorted by city, then name:

Boston - Alice Brown - Age: 28

Boston - Mike Wilson - Age: 32

New York - Bob Johnson - Age: 35

New York - John Doe - Age: 30

🔹 Mixed ASC and DESC

Combine ascending and descending order for different columns. This allows complex sorting like newest items first, then alphabetically. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Sort by age descending, then name ascending
$sql = "SELECT name, age, email FROM users ORDER BY age DESC, name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Users by age (high to low), then name (A-Z):</h3>";
    while($row = $result->fetch_assoc()) {
        echo "Age: " . $row["age"] . " - " . $row["name"] . " - " . $row["email"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Users by age (high to low), then name (A-Z):

Age: 35 - Bob Johnson - [email protected]

Age: 30 - John Doe - [email protected]

Age: 28 - Alice Brown - [email protected]

Age: 22 - Charlie Davis - [email protected]

🔹 ORDER BY with WHERE

Combine filtering and sorting to get precisely organized results. WHERE filters first, then ORDER BY sorts the filtered results. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Filter users over 25 and sort by name
$sql = "SELECT name, age, city FROM users WHERE age > 25 ORDER BY name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Users over 25, sorted by name:</h3>";
    while($row = $result->fetch_assoc()) {
        echo $row["name"] . " - Age: " . $row["age"] . " - " . $row["city"] . "<br>";
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Users over 25, sorted by name:

Alice Brown - Age: 28 - Boston

Bob Johnson - Age: 35 - New York

John Doe - Age: 30 - New York

🔹 ORDER BY with LIMIT

Get the top N results by combining ORDER BY with LIMIT. This is perfect for leaderboards, recent posts, or top performers. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Get top 3 oldest users
$sql = "SELECT name, age FROM users ORDER BY age DESC LIMIT 3";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<h3>Top 3 oldest users:</h3>";
    $rank = 1;
    while($row = $result->fetch_assoc()) {
        echo "$rank. " . $row["name"] . " - Age: " . $row["age"] . "<br>";
        $rank++;
    }
} else {
    echo "No users found";
}

$conn->close();
?>

Output:

Top 3 oldest users:

1. Bob Johnson - Age: 35

2. Mike Wilson - Age: 32

3. John Doe - Age: 30

💡 Best Practices

  • Use ORDER BY at the end of your SQL query
  • Index columns used frequently in ORDER BY for better performance
  • Combine with LIMIT to improve performance on large datasets
  • Be specific with ASC/DESC for code clarity
  • Consider the data type when sorting (numbers vs strings)

🧠 Test Your Knowledge

What is the default sorting order if you don't specify ASC or DESC?