ORDER BY clause in SQLite is used to sort query results based on one or more columns. It supports ascending and descending order, making it easier to organize records when retrieving data using Python.
Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];
- column_name specifies the column used for sorting.
- ASC sorts values in ascending order and is the default.
- DESC sorts values in descending order.
Creating Table and Inserting Data
Let's create a students table containing student names, marks, and city names. We will use this table to demonstrate different sorting operations with Python's built-in sqlite3 module.
import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute("""
CREATE TABLE students (
name TEXT NOT NULL,
marks INTEGER NOT NULL,
city TEXT NOT NULL
)
""")
connection.executemany(
"INSERT INTO students (name, marks, city) VALUES (?, ?, ?)",
[
("Aarav", 85, "Delhi"),
("Riya", 92, "Mumbai"),
("Kabir", 78, "Delhi"),
("Meera", 88, "Pune"),
("Ishaan", 81, "Mumbai")
]
)
connection.commit()
Sorting Data In Ascending Order
ASC keyword arranges records from the smallest value to the largest. For text columns, it generally sorts values alphabetically according to the applicable collation.
Example: Display student names and marks in ascending order of marks.
query = """
SELECT name, marks
FROM students
ORDER BY marks ASC
"""
for row in connection.execute(query):
print(row)
Output
Explanation:
- ORDER BY marks ASC arranges students from the lowest marks to the highest.
- SELECT statement retrieves only the name and marks columns.
- Each returned row is printed as a tuple.
Sorting Data is Descending Order
DESC to arrange records from the largest value to the smallest. This is useful when displaying high scores, expensive products, or the latest values first when sorting a suitable date column.
Example: Display students with the highest marks first.
query = """
SELECT name, marks
FROM students
ORDER BY marks DESC
"""
for row in connection.execute(query):
print(row)
Output
Explanation:
- DESC sorts marks from highest to lowest.
- query changes the order of the returned results, not the physical order of records in the table.
Sorting Multiple Columns
It can contain multiple sorting expressions. SQLite sorts by the first expression and uses subsequent expressions to resolve ties or organize records within each group.
Example: Sort students by city in ascending order and marks in descending order within each city.
query = """
SELECT name, city, marks
FROM students
ORDER BY city ASC, marks DESC
"""
for row in connection.execute(query):
print(row)
Output
Explanation:
- city ASC groups cities in ascending alphabetical order.
- marks DESC places higher-scoring students first within each city.
- Each sorting expression can specify its own direction.
Combining WHERE and ORDER BY
WHERE clause filters records according to a condition, while ORDER BY sorts the matching results. Use WHERE before ORDER BY in the SQL statement.
Example: Display students scoring at least 85 marks, starting with the highest score.
query = """
SELECT name, marks
FROM students
WHERE marks >= 85
ORDER BY marks DESC
"""
for row in connection.execute(query):
print(row)
Output
Explanation:
- WHERE marks >= 85 selects students meeting the specified condition.
- ORDER BY marks DESC sorts the filtered records from highest to lowest.
Using ORDER BY with LIMIT
It restricts the number of rows returned. Combining it with ORDER BY is useful when retrieving the top-scoring students or a fixed number of records from a larger dataset.
Example: Retrieve the three students with the highest marks.
query = """
SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3
"""
for row in connection.execute(query):
print(row)
Output
Explanation:
- ORDER BY marks DESC ranks students by marks in descending order.
- LIMIT 3 returns at most three records from the sorted results.