SQL ORDER BY: Sort Rows ASC or DESC

The SQL ORDER BY clause sorts query results. Ascending order is the default, so these two queries are equivalent:

SELECT id, name, mark
FROM student
ORDER BY mark;

SELECT id, name, mark
FROM student
ORDER BY mark ASC;

To sort from highest to lowest, use DESC:

SELECT id, name, mark
FROM student
ORDER BY mark DESC;

SQL ORDER BY Syntax Top ↑

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

ASC means ascending order and is the default. DESC means descending order.

Ascending Order Top ↑

For a numeric column, ascending order moves from lower values to higher values:

SELECT id, name, mark
FROM student
ORDER BY mark ASC;

Descending Order Top ↑

Use DESC to return larger values first:

SELECT id, name, mark
FROM student
ORDER BY mark DESC;

This is useful for highest scores, newest dates, largest prices and similar rankings.

For top records, combine ORDER BY with LIMIT.

ORDER BY on Text Columns Top ↑

For text columns, ascending order follows the column's collation rules:

SELECT id, name, class
FROM student
ORDER BY class ASC;

Descending order reverses that sort:

SELECT id, name, class
FROM student
ORDER BY class DESC;

Sort by Multiple Columns Top ↑

SQL applies the sort columns from left to right. This query sorts by class first, then by mark within each class:

SELECT id, name, class, mark
FROM student
ORDER BY class ASC, mark DESC;

Each sort column can use its own direction.

For example, marks can be descending while names are ascending for equal marks:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, name ASC;

Add a Tie-breaker for Predictable Ordering Top ↑

If several rows have the same value in the first sort column, their relative order is not guaranteed unless another sort expression resolves the tie.

Instead of:

SELECT id, name, mark
FROM student
ORDER BY mark DESC;

use a unique or otherwise deterministic secondary column when stable output matters:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, id ASC;

This is especially important for pagination and top-N queries.

ORDER BY with WHERE Top ↑

The WHERE clause filters rows first; ORDER BY sorts the rows that remain.

SELECT id, name, class, mark
FROM student
WHERE class = 'Four'
ORDER BY mark DESC, id ASC;

ORDER BY with LIMIT Top ↑

To return the three highest marks:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, id ASC
LIMIT 3;

Without ORDER BY, LIMIT 3 means only "return three rows"; it does not mean the highest three rows.

See also highest and second-highest record examples.

Sort by an Alias or Expression Top ↑

MySQL can sort using an expression or an alias defined in the SELECT list.

SELECT id,
       name,
       100 - mark AS difference
FROM student
ORDER BY difference DESC;

This avoids creating or updating an extra column merely for display-time sorting.

Sort Numbers Stored in a VARCHAR Column Top ↑

If numeric-looking values are stored in a text column, normal text sorting can produce an unexpected sequence such as 100, 18, 40, 6, 82.

For an existing VARCHAR column named diff, convert the value for sorting:

SELECT id, name, diff
FROM student
ORDER BY CAST(diff AS UNSIGNED) DESC;

See the MySQL CAST and type-conversion tutorial.

ORDER BY and NULL Values in MySQL Top ↑

In MySQL, NULL values normally sort before non-NULL values in ascending order and after non-NULL values in descending order.

SELECT id, name, class
FROM student3
ORDER BY class ASC;

If you need a different placement, sort first by a NULL expression and then by the column:

SELECT id, name, class
FROM student3
ORDER BY (class IS NULL) ASC,
         class ASC;

This places non-NULL class values before NULL values. See SQL NULL values.

Safe User-selected Sorting in PHP Top ↑

PDO placeholders bind data values, not SQL identifiers or keywords. This does not work:

<?php
$stmt=$dbo->prepare(
    "SELECT id,name,mark
     FROM student
     ORDER BY :sort :direction"
);

If users can choose the sort column or direction, select them from strict application-controlled allowlists:

<?php
$allowed_columns=[
    'name' => 'name',
    'class' => 'class',
    'mark' => 'mark'
];

$allowed_directions=[
    'asc' => 'ASC',
    'desc' => 'DESC'
];

$sort_key=$_GET['sort'] ?? 'name';
$direction_key=strtolower(
    $_GET['direction'] ?? 'asc'
);

$sort=$allowed_columns[$sort_key] ?? 'name';
$direction=$allowed_directions[$direction_key] ?? 'ASC';

$sql="SELECT id,name,class,mark
      FROM student
      ORDER BY $sort $direction, id ASC";

$stmt=$dbo->query($sql);

See changing record display order from user input for the practical interface idea.

ORDER BY after UNION Top ↑

When combining rows from two sources with UNION ALL, the final ORDER BY sorts the combined result.

SELECT dt, topic_id, userid
FROM (
    SELECT topic_id, rdtp AS dt, userid
    FROM forum_reply

    UNION ALL

    SELECT topic_id, tdtp AS dt, userid
    FROM forum_topics
) AS t
ORDER BY dt DESC
LIMIT 10;

This returns the ten most recent rows across both sources, assuming the date-time values are comparable.

ORDER BY Performance Notes Top ↑

• Sorting large result sets can require extra work and memory.
• An appropriate index can help some ORDER BY queries, depending on the WHERE clause, index order and query plan.
• Expressions such as CAST(column AS UNSIGNED) can make index-based ordering less straightforward.
• For pagination, use a deterministic ORDER BY so rows do not move unpredictably between pages.
• Use EXPLAIN when a production query is slow rather than assuming every ORDER BY requires a new index.

Common ORDER BY Mistakes Top ↑

Assuming rows have a natural order Top ↑

Without ORDER BY, the database is free to return rows in any order allowed by the execution plan.

Forgetting a tie-breaker Top ↑

If many rows share the same sort value, add another column such as a unique ID when deterministic ordering matters.

Using DESC only on the first column by assumption Top ↑

Each ORDER BY expression has its own direction. Write the required ASC or DESC explicitly when mixing directions.

Sorting numeric values stored as text Top ↑

Text values sort lexically. Use the correct numeric datatype whenever possible or CAST legacy text values when required.

Using LIMIT without ORDER BY for "top" records Top ↑

LIMIT restricts row count. ORDER BY defines which rows are highest, lowest, newest or otherwise first.

Binding column names as PDO parameters Top ↑

Prepared-statement placeholders are for data values, not identifiers or ASC/DESC keywords. Use an allowlist for user-selected sorting.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: