MySQL Order By

La Lakshmi Priya Updated 04 Nov 2026
5 min read ·Lesson 220 of 229
« Previous Next »

MySQL Order By Sorting Query Results In simple terms: A database does not guarantee any particular order for the rows it returns. If you want products sorted by price, customers alphabetically, or orders newest first, you must say so explicitly with ORDER BY. Sorting in the database is fast and avoids loading everything into PHP just to sort it there.

Technically: ORDER BY sorts the result set by one or more columns or expressions, each in ascending (ASC, the default) or descending (DESC) order. It is applied after WHERE filtering and before LIMIT. Without ORDER BY, MySQL returns rows in whatever order is most convenient internally, which can change between queries.

The examples use the products from MySQL Insert Multiple:

id sku name price stock category 1 PEN-01 Gel Pen 20.00 120 stationery 2 NB-A5 Notebook A5 60.00 45 stationery 3 LAMP-01 Desk Lamp 1299.00 8 electronics 4 BAG-02 Backpack 899.00 0 bags 5 MOUSE-01 Wireless Mouse 599.00 25 electronics Syntax SELECT columns FROM table WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC] LIMIT n; The clauses must appear in this order.

Sorting by One Column <?php require DIR . '/src/db.php';

$result = db()->query('SELECT name, price FROM products ORDER BY price DESC');

foreach ($result as $row) { printf("%-15s ₹%8s\n", $row['name'], $row['price']); } Output Desk Lamp ₹ 1299.00 Backpack ₹ 899.00 Wireless Mouse ₹ 599.00 Notebook A5 ₹ 60.00 Gel Pen ₹ 20.00 DESC sorts from highest to lowest. Because price is a DECIMAL column, MySQL sorts it numerically — 1299.00 correctly comes before 899.00. (Sorting numbers stored in a VARCHAR column would compare them as text, putting "899" before "1299"; choose proper column types.)

Sorting by Several Columns When rows have equal values in the first column, the second column decides, and so on:

<?php require DIR . '/src/db.php';

$result = db()->query('SELECT category, name, price FROM products ORDER BY category ASC, price DESC');

foreach ($result as $row) { printf("%-12s %-15s ₹%8s\n", $row['category'], $row['name'], $row['price']); } Output bags Backpack ₹ 899.00 electronics Desk Lamp ₹ 1299.00 electronics Wireless Mouse ₹ 599.00 stationery Notebook A5 ₹ 60.00 stationery Gel Pen ₹ 20.00 Products are grouped alphabetically by category; within each category, the most expensive comes first. Each column has its own direction — ASC for the category, DESC for the price.

Sorting by an Expression ORDER BY can use calculated values, including column aliases from the SELECT list:

<?php require DIR . '/src/db.php';

$result = db()->query( 'SELECT name, price * stock AS stock_value FROM products ORDER BY stock_value DESC, name' );

foreach ($result as $row) { printf("%-15s ₹%10s\n", $row['name'], number_format((float) $row['stock_value'], 2)); } Output Wireless Mouse ₹ 14,975.00 Desk Lamp ₹ 10,392.00 Notebook A5 ₹ 2,700.00 Gel Pen ₹ 2,400.00 Backpack ₹ 0.00 The value of stock per product (price × quantity) is calculated and sorted in one query. Adding name as a second sort column makes the order deterministic if two products have the same value.

Custom Orders With FIELD() Sometimes the desired order is neither alphabetical nor numeric — for example, showing categories in the order the business prefers:

<?php require DIR . '/src/db.php';

$result = db()->query( "SELECT category, name FROM products ORDER BY FIELD(category, 'electronics', 'stationery', 'bags'), id" );

foreach ($result as $row) { echo "{$row['category']}: {$row['name']}\n"; } Output electronics: Desk Lamp electronics: Wireless Mouse stationery: Gel Pen stationery: Notebook A5 bags: Backpack FIELD(value, a, b, c) returns the position of value in the list (1, 2, 3, …), which is then used for sorting. Values not in the list get 0 and would appear first. The same technique sorts order statuses in workflow order (pending, paid, shipped, …).

How NULL Values Are Sorted In MySQL, NULL is treated as lower than any other value: it comes first with ASC and last with DESC. To put customers without a city at the end of an ascending list:

SELECT name, city FROM customers ORDER BY city IS NULL, city, name; city IS NULL evaluates to 0 for rows with a city and 1 for rows without, so rows with a city sort first; within each group, the remaining columns decide.

Letting Users Choose the Sort Order Column names and ASC/DESC cannot be prepared-statement parameters (see MySQL Prepared). User choices must therefore be mapped to SQL written in the code:

<?php require DIR . '/src/db.php';

function orderByClause(?string $sort, ?string $direction): string { $columns = [ 'name' => 'name', 'price' => 'price', 'newest' => 'created_at', ];

$column = $columns[$sort ?? ''] ?? 'name'; $dir = strtolower($direction ?? '') === 'desc' ? 'DESC' : 'ASC';

return "ORDER BY $column $dir, id ASC"; }

// Simulates: products.php?sort=price&dir=asc $sql = 'SELECT name, price FROM products ' . orderByClause('price', 'asc'); echo $sql . "\n";

foreach (db()->query($sql) as $row) { echo " {$row['name']} (₹{$row['price']})\n"; }

echo orderByClause('price; DROP TABLE products', 'sideways') . "\n"; Output SELECT name, price FROM products ORDER BY price ASC, id ASC Gel Pen (₹20.00) Notebook A5 (₹60.00) Wireless Mouse (₹599.00) Backpack (₹899.00) Desk Lamp (₹1299.00) ORDER BY name ASC, id ASC How It Works Only keys of the $columns allow-list are accepted; anything else falls back to sorting by name. The direction can only become ASC or DESC. A malicious value such as price; DROP TABLE products is simply ignored — it never reaches the SQL. The extra id ASC makes the order stable, which is essential for pagination (see Pagination with PHP). Performance Notes Situation Advice Sorting large tables by a column An index on that column (or on WHERE + ORDER BY columns) lets MySQL avoid a full sort ORDER BY RAND() Sorts the whole table randomly — very slow for large tables; pick random IDs in PHP or use other strategies Sorting in PHP after fetching everything Wasteful; let the database sort and limit EXPLAIN SELECT ... shows whether MySQL uses an index or performs a "filesort".

Common Mistakes Relying on the Default Order Effect: Rows appear in a different order after inserts, updates, or server upgrades.

Fix: Always specify ORDER BY when the order matters.

Placing User Input in ORDER BY Effect: SQL injection.

Fix: Map choices to allow-listed columns and directions.

Sorting Numbers Stored as Text Effect: "100" sorts before "20".

Fix: Use numeric column types.

Non-Unique Sort Keys With Pagination Effect: Rows with equal values may appear on two pages or on none.

Fix: Add a unique tie-breaker such as id.

Best Practices Specify ORDER BY whenever the order of results matters. Add a unique tie-breaker column for deterministic results. Sort in the database, not in PHP. Use allow-lists for user-selectable sorting. Index columns that are frequently used for sorting large tables. Official reference: https://www.php.net/manual/en/mysqli.query.php

← Previous
PHP Type Juggling
Next →
MySQL Delete Data

More in PHP

See all
Post image PHP
PDO Fetch Modes
PDO Fetch Modes What Are Fetch Modes? In simple terms: After a query runs, PDO hands you the result row…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Prepared Statements
PDO Prepared Statements Why Prepared Statements? In simple terms: A prepared statement keeps the SQL co…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Connect
PDO Connect Connecting With PDO In simple terms: To use a database through PDO, you create a PDO object…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Introduction
PDO Introduction What Is PDO? In simple terms: PDO (PHP Data Objects) is PHP's standard way to talk to …
La Lakshmi Priya 04 Nov 2026

0 Comments

Reviewed before they appear

No comments yet.

PHP
PHP Home PHP Switch PHP Match Alternative Syntax PHP Loops While Loop Do While Loop For Loop Foreach Loop Nested Loops Break Statement PHP Intro PHP Install PHP Built-in Web Server PHP Syntax PHP Tags PHP and HTML PHP Comments PHP Multiline Comments PHP Variables Variables Scope PHP Casting Logical Operators Null Coalescing Operator Spaceship Operator Operator Precedence Continue Statement Modify Strings PHP If...Else...Elseif PHP If Modify Strings PHP If Operators PHP Functions Concatenate Strings PHP If...Else User-defined Functions Slicing Strings Function Arguments PHP Shorthand if Escape Characters Default Argument Values PHP Nested if Pass by Reference String Interpolation Named Arguments PHP File Upload Variadic Functions Return Values Search in Strings Multibyte Strings Type Declarations in Functions PHP Numbers PHP Math PHP Floats PHP Random Numbers PHP Constants PHP Properties PHP Methods PHP Constructor Constructor Property Promotion PHP Cookies PHP Destructor PHP Access Modifiers Readonly Properties PHP Sessions PHP Inheritance Method Overriding PHP Filters The final Keyword CSRF Protection PHP Filters Advanced Session Security Secure Cookies PHP Magic Constants File Upload Security PHP Callback Functions PHP Operators Filesystem Security PHP JSON Arithmetic Operators Error Reporting in Production Hiding PHP Assignment Operators PHP Configuration (php.ini) Comparison Operators PHP Command Line (CLI) PHP Exceptions Increment / Decrement Operators Migrating to PHP 8 PHP Form Validation PHP Form Required PHP Form URL/E-mail Error Handling Form Input Sanitizing Escaping Output (htmlspecialchars) Custom Error Handler Checkbox, Radio and Select Inputs Sticky Forms PHP Form Complete PHP Date and Time Date Formatting Error Logging HTTP Headers and Redirects Output Buffering Sending Email PHP cURL Consuming REST APIs PHP Generators AJAX PHP AJAX Database AJAX XML AJAX Live Search AJAX Poll AJAX with JSON Security Introduction Password Hashing SQL Injection Prevention XSS Prevention Garbage Collection Project: Simple Blog PHP What is OOP PHP Classes/Objects The $this Keyword Access Array Items Update Array Items Add Array Items Remove Array Items Sorting Arrays Multidimensional Arrays Array Destructuring Array Spread Operator Search in Arrays Merge Arrays PHP Abstract Classes PHP Interfaces Polymorphism PHP Traits PHP Static Methods PHP Static Properties Late Static Binding Magic Methods Object Cloning MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Where Debugging PHP Project: Image Upload Gallery $_SESSION $_ENV PHP RegEx RegEx Patterns Heredoc and Nowdoc RegEx Modifiers preg_match preg_replace preg_split PHP RegEx Functions Nullable Types Mixed and Never Types Nullsafe Operator PHP Attributes PHP Fibers Readonly Classes Property Hooks (PHP 8.4) Asymmetric Visibility (PHP 8.4) Reflection API SPL Data Structures Composer PHP Form Handling PDO Transactions PDO Error Handling PHP XML Parsers PHP SimpleXML Parser PHP SimpleXML - Get PHP XML Expat Parser PHP DOM Parser PHP XMLReader PHP XMLWriter AJAX Intro PHP Class Constants Encryption with Sodium Recursive Functions Variable Functions Anonymous Functions Closures Arrow Functions First-class Callable Syntax PHP Arrays Indexed Arrays Associative Arrays Create Arrays Variables Scope Static Variables Variable Variables PHP Echo / Print PHP printf / sprintf PHP Data Types PHP Booleans PHP NULL PHP Strings String Functions array_map / array_filter / array_reduce Array Functions PHP Superglobals $GLOBALS What Is `$_SERVER` ? $_REQUEST $_POST $_GET $_FILES $_COOKIE PHP Type Juggling MySQL Order By MySQL Delete Data MySQL Update Data MySQL Limit Data MySQL Joins with PHP Pagination with PHP PDO Introduction PDO Connect PDO Prepared Statements PDO Fetch Modes
Ask about this post
AI Ask about this post

Ask questions about MySQL Order By and get answers drawn from it.

Signed-in readers only.