SQLite ORDER BY Clause

The ORDER BY clause in SQLite allows you to sort the result set of a SELECT statement. You can sort the results in ascending or descending order based on one or more columns.

Let’s first create a table called transactions and insert some sample data into it:

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  customer_name TEXT, 
  order_date DATE,
  amount NUMERIC  
);

INSERT INTO transactions VALUES
  (1, 'John', '2022-10-05', 150.50),
  (2, 'Mary', '2022-10-02', 72.50),
  (3, 'David', '2022-10-06', 89.90), 
  (4, 'Sara', '2022-10-01', 210.20),
  (5, 'Mark', '2022-10-04', 185.35);

This gives us a table with some sample customer transactions. We’ll use this table to demo the rest of the examples.

SQLite ORDER BY Ascending

To sort the results in ascending order, use the ORDER BY clause followed by the column name:

SELECT *
FROM transactions
ORDER BY order_date;

This would give:

id          customer_name   order_date     amount
4           Sara            2022-10-01     210.20
2           Mary            2022-10-02     72.50  
5           Mark            2022-10-04     185.35
1           John            2022-10-05     150.50
3           David           2022-10-06     89.90

The results are sorted by order_date in ascending chronological order.

You can also order by multiple columns:

SELECT *
FROM transactions
ORDER BY order_date, customer_name;

This would give:

id          customer_name   order_date     amount
4           Sara            2022-10-01     210.20
2           Mary            2022-10-02     72.50
1           John            2022-10-05     150.50  
5           Mark            2022-10-04     185.35
3           David           2022-10-06     89.90

First sorted by order_date, and then by customer_name alphabetically.

SQLite ORDER BY Descending

To sort the results in descending order, use the DESC keyword:

SELECT * 
FROM transactions
ORDER BY order_date DESC;

This would give:

id          customer_name   order_date     amount
3           David           2022-10-06     89.90
1           John            2022-10-05     150.50
5           Mark            2022-10-04     185.35
2           Mary            2022-10-02     72.50 
4           Sara            2022-10-01     210.20

The results are sorted by order_date in descending chronological order.

You can also combine ASC and DESC:

SELECT *
FROM transactions  
ORDER BY order_date DESC, customer_name ASC;

This would give:

id          customer_name   order_date     amount
3           David           2022-10-06     89.90
1           John            2022-10-05     150.50
5           Mark            2022-10-04     185.35
2           Mary            2022-10-02     72.50
4           Sara            2022-10-01     210.20 

Sorted by order_date descending first, then customer_name alphabetically ascending.

Ordering by Column Number in SQLite

Instead of the column name, you can also specify the column number in the ORDER BY clause:

SELECT *
FROM transactions
ORDER BY 3;

This would give:

id          customer_name   order_date     amount  
4           Sara            2022-10-01     210.20
2           Mary            2022-10-02     72.50
1           John            2022-10-05     150.50
5           Mark            2022-10-04     185.35
3           David           2022-10-06     89.90

Sorted based on the 3rd column in the table, which is order_date.

This can be useful when you want to order by a column that is not directly named in the SELECT statement.

ORDER BY on Aggregate Functions

ORDER BY works with aggregate functions like COUNT, MAX, MIN etc:

SELECT customer_name, MAX(amount)
FROM transactions  
GROUP BY customer_name
ORDER BY MAX(amount) DESC; 

This would give:

customer_name  MAX(amount)
Sara           210.20
Mark           185.35
John           150.50
Mary           72.50
David          89.90

Returned customer names ordered by highest total order amount.

ORDER BY and LIMIT

ORDER BY is often used together with SQLite LIMIT to get the top or bottom rows:

SELECT *
FROM transactions
ORDER BY amount DESC
LIMIT 2;

This would give:

id          customer_name   order_date     amount
4           Sara            2022-10-01     210.20 
5           Mark            2022-10-04     185.35

The top 2 highest value transactions.

Summary

The ORDER BY clause allows flexible control over sorting query results in SQLite. Key points:

• Use ORDER BY column to sort ascending
• Use ORDER BY column DESC to sort descending
• Sort by multiple columns by listing them comma-separated
• Use with LIMIT to get top/bottom rows
• Works with aggregate functions like MAX, MIN etc