Module 4

SQL ORDER BY: Basic Sorting with ASC and DESC

A step-by-step, hands-on SQL practice lesson — with a live query playground

SELECT * FROM products ORDER BY price DESC;

The first time I ran a report for a manager and handed over a list of products in whatever order MySQL felt like giving them to me, I got a very fair question back: "Can you sort this by price?" I could — I just didn't know the keyword yet. That keyword is ORDER BY, and once I learned it, it became one of the clauses I use in almost every single query I write, right alongside WHERE. This lesson walks through exactly how it works.

Introduction

What You'll Learn — and Why It Matters

My experience

Before I knew ORDER BY, I assumed a table's row order meant something — that it reflected the order things were added, or some kind of natural priority. It doesn't. A database is free to hand rows back in whatever order is most convenient for it, which is almost never the order a person actually wants to read them in. ORDER BY is the clause that puts you back in control of that.

🎯 Objective

By the end of this lesson, you will be able to:

Sort query results using ORDER BY on any column.
Understand that ASC (ascending) is the default, and use DESC when you need the opposite.
Sort by more than one column at once, and control the direction of each independently.
Combine ORDER BY with WHERE, aliases, and other clauses from earlier modules.

⚠️ What to Avoid

Common mistakes

Assuming rows come back sorted "naturally" without an ORDER BY clause — they don't, reliably.
Forgetting that leaving off ASC or DESC doesn't mean "no sorting" — it means ascending, silently.
Writing multiple sort columns without a direction on each one and assuming they all inherit the first column's direction — they don't.

💡 Important to Know

Before you start

I think of ORDER BY as the very last thing SQL does to your results, logically speaking — it runs after WHERE has already filtered the rows down. That's exactly why it's always written at the end of the query, and why you can sort by columns you've filtered on, aliased, or even calculated.

Requirements

What You'll Need Before Starting

Operating SystemWindows, macOS, or Linux — everything here works identically no matter which one you're on.

Database ServerMySQL installed and running. See How to Install MySQL if you haven't set it up yet.

Optional but recommendedXAMPP with phpMyAdmin, if you'd rather work visually than at the command line. See How to Install XAMPP.

Prior knowledgeComfortable with SELECT and WHERE, starting with Module 1.

Nothing needs to be installed to try the live playground further down this page — it runs directly in your browser.

Practice

Step-by-Step Practice

I'll use a small products list for this one — sorting is easiest to feel out on a table that has both a text column and a couple of numeric columns to play with.

1 Open MySQL (CLI or phpMyAdmin)

Open a terminal and connect with the MySQL command-line client, or open phpMyAdmin at http://localhost/phpmyadmin/.

mysql -u root -p

The basic syntax pattern for this lesson looks like this:

SELECT columns FROM table
ORDER BY column ASC | DESC;

2 Create a Database and Verify It

CREATE DATABASE module4_practice;
SHOW DATABASES;
USE module4_practice;

Now create a products table and verify it — I always run DESCRIBE right after, just to confirm every column looks the way I expect before I put any data in it.

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(30),
  price DECIMAL(8,2),
  stock_quantity INT
);
SHOW TABLES;
DESCRIBE products;

3 Insert Sample Data

A mix of categories and prices, on purpose — it makes the sorting differences easy to spot.

INSERT INTO products (id, name, category, price, stock_quantity) VALUES
(1, 'Wireless Mouse', 'Electronics', 12.99, 150),
(2, 'Office Chair', 'Furniture', 89.50, 20),
(3, 'Notebook', 'Stationery', 2.49, 500),
(4, 'Bluetooth Speaker', 'Electronics', 45.00, 60),
(5, 'Desk Lamp', 'Furniture', 24.75, 80);

4 Sort Ascending (the Default): ASC

Cheapest products first. Notice this is exactly what you'd get by leaving ASC off entirely.

SELECT name, price FROM products
ORDER BY price ASC;

5 Sort Descending: DESC

The most expensive products first — the direction you'll reach for constantly for "top N" style questions.

SELECT name, price FROM products
ORDER BY price DESC;

6 Sort a Text Column Alphabetically

ORDER BY isn't just for numbers — text columns sort alphabetically the same way.

SELECT name, category FROM products
ORDER BY name ASC;

7 Sort by Multiple Columns

Group by category first, then show the most expensive item in each category first. Each column gets its own direction.

SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

8 Combine ORDER BY with WHERE

WHERE still does the filtering — ORDER BY just decides what order the surviving rows come back in.

SELECT name, price FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

9 Combine with an Alias

You can sort by a column's alias too, which keeps longer queries easier to read.

SELECT name AS product_name, stock_quantity AS in_stock
FROM products
ORDER BY in_stock DESC;

Troubleshooting

Common Problems and Solutions

Every one of these is a mistake I've made myself at some point — sorting looks simple right up until it quietly isn't.

Problem 1: My results seem to come back in a random order without ORDER BY

I assumed the table kept rows in the order I inserted them. Sometimes it looked that way, sometimes it didn't.

SolutionWithout ORDER BY, a database makes no promise about row order at all. If order matters — and it usually does for anything a person will read — always add ORDER BY explicitly.

Problem 2: I left off ASC/DESC and got ascending order by accident

I wanted the highest prices first and forgot the keyword — the query ran fine, but the order was backwards from what I wanted.

SolutionASC is the default when no direction is given, not "unsorted." Always write DESC explicitly whenever you want the reverse.

Problem 3: My second sort column didn't apply the direction I expected

I wrote ORDER BY category, price DESC assuming both columns would sort descending, and category came back ascending instead.

SolutionEach column in an ORDER BY list needs its own direction. DESC only applies to the column directly in front of it — write ORDER BY category DESC, price DESC if you want both reversed.

Problem 4: Text sorting didn't look "alphabetical" to me

A product named "iPad" sorted after "Zebra Print Case" in a case-sensitive setup, which looked wrong at a glance.

SolutionDepending on the database's collation settings, sorting can be case-sensitive or case-insensitive. Most default MySQL setups sort case-insensitively, but it's worth checking your database's collation if the order looks off.

Problem 5: A NULL price threw off my sort order

A missing price value showed up first in my ascending sort, ahead of every real number.

SolutionThis connects back to Module 10 — NULL values are grouped together during sorting, and in MySQL they sort as if they come before everything else in ascending order. If that matters, filter them out first with WHERE price IS NOT NULL.

Conclusion

What You Should Take Away

Sorting turns out to be one of those clauses that's genuinely simple once it clicks — ascending is the default, DESC reverses it, and multiple columns just need their own direction each. You should now be comfortable reaching for ORDER BY any time the order of your results actually matters, which in practice is most of the time.

In Module 5, we'll build on this by looking at LIMIT — a clean way to pull back just the top few rows of a sorted result instead of the whole table.

Try It Live

🧪 Live SQL Playground

A real SQLite database running entirely in your browser (via WebAssembly) — nothing is sent to a server. Practice using ORDER BY on the products table below, or pick an example query to get started.

products id INTEGER PK
name TEXT
category TEXT
price REAL
stock_quantity INTEGER

Loading SQL engine…

Run a query to see results here.

Related Lessons

Continue the Series

Module 1: Mastering the SQL SELECT Statement (A Step-by-Step Guide)

How to Install XAMPP Step by Step for Beginners

How to Install MySQL Step by Step for Beginners

FAQ

Frequently Asked Questions

1. What does ORDER BY actually do?

It controls the order rows are returned in — without it, a database makes no promise about row order at all, even if it often looks stable in practice.

2. What's the default sort direction?

Ascending (ASC) — smallest numbers first, or A-to-Z for text — if you don't specify a direction at all.

3. How do I sort from highest to lowest?

Add DESC after the column name, like ORDER BY price DESC.

4. Can I sort by more than one column?

Yes — list the columns separated by commas, and give each one its own ASC or DESC, since directions don't carry over from one column to the next.

5. Can I combine ORDER BY with WHERE?

Yes, and it's extremely common — WHERE filters the rows first, and ORDER BY decides what order the remaining rows come back in.

6. Can I sort by a column alias?

Yes, most databases including MySQL let you ORDER BY the alias you defined in the SELECT list.

7. Does ORDER BY work on text columns?

Yes, text sorts alphabetically the same way numbers sort numerically, though the exact behavior can depend on your database's collation settings.

8. Where do NULL values end up when sorting?

They're grouped together, and in MySQL's ascending order they typically appear first. Filter them out with IS NOT NULL if you don't want them mixed in.

9. Does ORDER BY change the actual data in the table?

No — it only changes the order the results are displayed in for that query. The underlying table is never reordered or modified.

10. Where should ORDER BY go in my query?

At the very end, after WHERE and any other clauses — it's the last thing applied to your results.