🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

ORDER BY Basics: How to Freely Sort Data with SQL

Product listings on websites, blog article indexes, user rankings… Much of the web content we encounter daily is displayed in a specific order. This ordering of data is handled by SQL’s ORDER BY clause.

This article is aimed at web creators who are just starting to learn SQL or those who want a refresher on the basics. We’ll thoroughly explain how to use ORDER BY. With the theme “Just copy and it works!”, we’ve prepared plenty of sample code. Try it out yourself and experience the fun of manipulating data freely!

Let’s Get Started! Prepare Sample Data First

Practice over theory! Let’s dive right into writing SQL. In this article, we’ll use a fictional online store called “Web Creator’s Mart” and its product table (products) as an example.

The following SQL statements will create this table and insert sample data. The rest of the code assumes that this table already exists, so if you want to try it in your own environment, be sure to run this SQL first.

-- Create the products table
CREATE TABLE products (
  id INT,
  name VARCHAR(255),
  category VARCHAR(255),
  price INT,
  release_date DATE
);

-- Insert sample data
INSERT INTO products (id, name, category, price, release_date) VALUES
(1, 'Fluffy Cushion', 'Interior', 3200, '2023-04-15'),
(2, 'Focus Ear Muffs', 'Gadget', 7800, '2023-11-01'),
(3, 'Idea-Sparking Notebook', 'Stationery', 1200, '2022-08-20'),
(4, 'Glowing Keyboard', 'Gadget', 15000, '2023-06-30'),
(5, 'Ultimate Comfort Chair', 'Interior', 32000, '2022-11-10'),
(6, 'Smooth Ballpoint Pen', 'Stationery', 1200, '2023-09-01'),
(7, 'Creator’s Mug', 'Tableware', 2500, '2023-04-15');

This table contains columns for product ID (id), product name (name), category (category), price (price), and release date (release_date). Ready to go? Let’s dive into using ORDER BY!

ORDER BY Basics: Let’s Try Sorting in Ascending Order

The most basic use of ORDER BY is to sort based on a specific column in ascending order (smallest to largest, oldest to newest). For example, when you want to display a product list sorted by lowest price.

The syntax is very simple—just add ORDER BY column_name at the end of the SELECT statement.

SELECT * FROM products ORDER BY price;

When you execute this SQL, the data will be sorted by the price column in ascending order (from the lowest value). By the way, you can explicitly specify ascending order using the keyword ASC (short for Ascending), but since ORDER BY defaults to ascending, ASC is often omitted.

-- This gives the exact same result as the previous query
SELECT * FROM products ORDER BY price ASC;

Reverse Order is Easy! Sorting in Descending Order with DESC

Sometimes you may want to sort in the opposite direction—for example, from the highest price or the most recent release date. In such cases, use the DESC keyword (short for Descending).

Just add DESC after the column name—it's that simple!

-- Sort by price in descending order
SELECT * FROM products ORDER BY price DESC;

This will bring the most expensive item, “Ultimate Comfort Chair,” to the top. Likewise, you can sort by release date in descending (newest first) order.

-- Sort by release date in descending order
SELECT * FROM products ORDER BY release_date DESC;

While ASC is optional, you must specify DESC if you want descending order, so don’t forget it.

Advanced Example ①: Sorting by Multiple Conditions

One of the powerful features of ORDER BY is that it allows sorting by multiple conditions. For example, you might want to sort by category first, and within each category, sort by price in ascending order.

In such cases, simply specify multiple columns separated by commas (,) in the ORDER BY clause. The column listed first will be the primary sort key.

-- Sort by category ascending, then by price ascending
SELECT * FROM products ORDER BY category, price;

Executing this SQL will first sort by category alphabetically (e.g., Gadget → Interior → Stationery...), and then within the “Stationery” category, by price in ascending order.

You can also specify ascending or descending order individually for each column. A common use case in e-commerce is to sort by newest release date, and if multiple items have the same date, show the higher priced ones first.

-- Sort by release date descending (newest first), then by price descending (highest first)
SELECT * FROM products ORDER BY release_date DESC, price DESC;

In the sample data, “Fluffy Cushion” and “Creator’s Mug” share the same release date (2023-04-15). Running this SQL will display the more expensive “Fluffy Cushion” before the mug. Mastering multi-column sorting really expands your options for presenting data!

💻 Try It Yourself! Run SQL in Your Browser

Now that we've looked at many SQL examples, the best way to learn is by doing.
So here’s a special HTML file that lets you run SQL directly in your browser—no setup required!

Copy all the code below into a file named something like index.html and open it in your browser. You’ll instantly have your own SQL playground. Try out different ORDER BY variations you learned in this article!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL Practice with ORDER BY</title>
  <style>
    body { font-family: sans-serif; line-height: 1.6; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1, h2 { color: #444; }
    textarea { width: 100%; height: 150px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; }
    button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
    button:hover { background-color: #0056b3; }
    #result-container { margin-top: 1.5rem; }
    table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
    th { background-color: #f2f2f2; }
    #error-message { color: red; font-weight: bold; }
  </style>
</head>
<body>

  <h1>Learn SQL by Running It! ORDER BY Playground</h1>
  <p>Enter your SQL in the textarea below and press the "Run SQL" button. The query will run against the <code>products</code> table.</p>

  <textarea id="sql-input">-- Enter your SQL here! Try different patterns!
SELECT * FROM products ORDER BY price DESC;</textarea>
  <button onclick="executeQuery()">Run SQL</button>

  <h2>Results</h2>
  <div id="result-container"></div>
  <div id="error-message"></div>

  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
  <script>
    let db;

    async function initDb() {
      const SQL = await initSqlJs({
        locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
      });
      db = new SQL.Database();
      
      // Create table and insert data
      const createTableSQL = `
        CREATE TABLE products (
          id INT,
          name VARCHAR(255),
          category VARCHAR(255),
          price INT,
          release_date DATE
        );
        INSERT INTO products (id, name, category, price, release_date) VALUES
        (1, 'Fluffy Cushion', 'Interior', 3200, '2023-04-15'),
        (2, 'Focus Ear Muffs', 'Gadget', 7800, '2023-11-01'),
        (3, 'Idea-Sparking Notebook', 'Stationery', 1200, '2022-08-20'),
        (4, 'Glowing Keyboard', 'Gadget', 15000, '2023-06-30'),
        (5, 'Ultimate Comfort Chair', 'Interior', 32000, '2022-11-10'),
        (6, 'Smooth Ballpoint Pen', 'Stationery', 1200, '2023-09-01'),
        (7, 'Creator’s Mug', 'Tableware', 2500, '2023-04-15');
      `;
      db.run(createTableSQL);
      
      // Run initial query
      executeQuery();
    }

    function executeQuery() {
      const sqlInput = document.getElementById('sql-input').value;
      const resultContainer = document.getElementById('result-container');
      const errorMessage = document.getElementById('error-message');
      
      resultContainer.innerHTML = '';
      errorMessage.innerHTML = '';

      try {
        const results = db.exec(sqlInput);
        if (results.length > 0) {
          resultContainer.appendChild(createTable(results[0]));
        } else {
          resultContainer.innerHTML = '<p>Query executed successfully, but no result set was returned (e.g., CREATE TABLE, INSERT).</p>';
        }
      } catch (e) {
        errorMessage.textContent = 'Error: ' + e.message;
      }
    }

    function createTable(result) {
      const table = document.createElement('table');
      const thead = document.createElement('thead');
      const tbody = document.createElement('tbody');
      const headerRow = document.createElement('tr');

      result.columns.forEach(colName => {
        const th = document.createElement('th');
        th.textContent = colName;
        headerRow.appendChild(th);
      });
      thead.appendChild(headerRow);

      result.values.forEach(rowData => {
        const tr = document.createElement('tr');
        rowData.forEach(cellData => {
          const td = document.createElement('td');
          td.textContent = cellData;
          tr.appendChild(td);
        });
        tbody.appendChild(tr);
      });

      table.appendChild(thead);
      table.appendChild(tbody);
      return table;
    }

    initDb();
  </script>
</body>
</html>

Useful Tips to Know! Key Points When Using ORDER BY

To wrap up, let’s go over a few things to be aware of when using ORDER BY, along with some handy techniques.

Handling NULL Values

When your data contains NULL values (i.e., missing or undefined), how does ORDER BY treat them? This behavior can vary slightly depending on the database system, but typically NULL values are grouped either at the beginning (NULLS FIRST) or at the end (NULLS LAST) of the result set. Since it can affect your sort order unexpectedly, it's a good idea to test it out when sorting columns that might include NULL.

About Performance

If you're sorting millions of records with ORDER BY, it can place a significant load on your database and slow down query performance. This is especially true if the column being sorted is not indexed. If you often sort by specific columns, consider setting up indexes in your database. This intermediate-level technique can drastically improve response speed and is worth keeping in mind as your datasets grow.

You Can Sort by Columns Not in the SELECT Clause

This might surprise you, but you can sort using a column that is not included in the SELECT clause. For example, you may want to display only product names and prices, but still sort the results by the most recent release date.

-- Only display name and price, but sort by release_date
SELECT name, price FROM products ORDER BY release_date DESC;

This flexibility—separating what data is displayed from how it's sorted—is one of the many powerful features of SQL.

Summary: Master ORDER BY and Level Up Your Data Skills!

In this article, we’ve covered the ORDER BY clause in SQL—from the basics to multi-column sorting and some advanced caveats. Let’s quickly review the key takeaways:

ORDER BY is a fundamental skill that helps present database results in a clearer and more user-friendly way. Make the most of the example code and interactive environment provided in this article to experiment with different sorting techniques. Once you master it, both web development and data analysis will become even more enjoyable!

Related Templates