MySQL Order By

Understand the idea

This query lists higher-stock products first and breaks stock ties by unique identifier. If a user selects the sort field, choose from a fixed allowlist of SQL fragments. Value placeholders cannot stand in for a column name or the ASC/DESC keyword. Do not concatenate arbitrary submitted sort text.

Where to try it

Use a local Python environment with mysql-connector-python and your own separate MySQL lab server. Read the lab setup below before running. This example is not connected to this website’s database and cannot run in the restricted online Python tool.

A worked example

import os
import mysql.connector
connection = mysql.connector.connect(
    host=os.environ.get("DB_HOST", "127.0.0.1"),
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    database=os.environ["DB_NAME"],
)
cursor = connection.cursor()
try:
    cursor.execute("SELECT product_id, name, stock FROM workshop_products ORDER BY stock DESC, product_id ASC")
    for row in cursor:
        print(row)
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

Read the result

Products are ranked by descending stock with a deterministic tie order.

Try one change

Add two rows with equal stock and confirm that the identifier controls their relative display order.

Local lab setup

Use a local Python virtual environment with mysql-connector-python installed and a separate MySQL lab server. Run the create-database and create-table lessons first, then insert a few lab products. Set DB_HOST, DB_USER, DB_PASSWORD, and DB_NAME in the environment of the process that runs the script. These snippets are complete driver examples; they have not been run against your database.