The ORDER BY clause in SQL is used to sort the results of a query based on one or more columns. In this tutorial, we’ll explore how to use ORDER BY with PostgreSQL in Python using the psycopg2 library to control the sorting of your data.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. Setting Up the Database

4. Using ORDER BY in SQL with Python

5. Sorting in Ascending vs Descending Order

6. Ordering by Multiple Columns

7. Full Working Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How to sort data using ORDER BY in PostgreSQL with Python

• How to order results in ascending (ASC) or descending (DESC) order

• How to sort by multiple columns

• How to write secure and dynamic sorting queries

✅ 2. Prerequisites

You need:

• Python installed

• PostgreSQL installed and running

• The psycopg2 library installed:

pip install psycopg2-binary

• A sample PostgreSQL table like students with some data

3. Setting Up the Database

Let's assume a table students is already created:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

Insert sample data:

INSERT INTO students (name, age, grade)
VALUES
('Alice', 14, '8th'),
('Bob', 13, '7th'),
('Carol', 15, '9th'),
('David', 14, '8th'),
('Eve', 13, '7th');

4. Using ORDER BY in SQL with Python

The ORDER BY clause lets you sort by any column. Default sorting is ascending (ASC).

Sort by Age (Ascending):

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

cur.execute("SELECT * FROM students ORDER BY age ASC")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

5. Sorting in Descending Order

To sort in descending order, use DESC.

cur.execute("SELECT * FROM students ORDER BY age DESC")

This will list students starting with the oldest first.

6. Ordering by Multiple Columns

You can order by more than one column.

Example: Order by age, then by name:

cur.execute("SELECT * FROM students ORDER BY age ASC, name ASC")

This will:

• Sort by age first (smallest to largest)

• If two students have the same age, sort those alphabetically by name

7. Full Working Example

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    # Order students by age descending
    cur.execute("SELECT * FROM students ORDER BY age DESC, name ASC")
    students = cur.fetchall()

    print("Students ordered by age (DESC), then name (ASC):")
    for s in students:
        print(f"ID: {s[0]}, Name: {s[1]}, Age: {s[2]}, Grade: {s[3]}")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

8. Tips and Common Pitfalls

Conclusion

The ORDER BY clause is a powerful way to control the presentation of your query results. By integrating it into your Python PostgreSQL applications using psycopg2, you can ensure that your data is always organized in the way users or processes expect.