Sorting Pagination

title: "Sorting with Pagination — Consistent Ordering Across Pages" description: "Sorting with pagination requires stable sort orders using unique tiebreaker columns to prevent items from appearing on multiple pages or disappearing." date: 2026-06-28 lastmod: 2026-06-28 weight: 17 tags: [apis, pagination] }

Sorting with pagination requires a stable sort order. Without a unique tiebreaker column, items with equal sort values may appear on multiple pages or disappear between pages.

What You'll Learn

• Why stable sorting matters for pagination
• Using tiebreaker columns
• Client-specified sort parameters

Why It Matters

Without stable sorting, paginated results are inconsistent. Items shift between pages, causing confusion and data integrity issues.

Code Examples

# Stable sorting with tiebreaker
@app.route('/users')
def list_users():
    sort_by = request.args.get('sort_by', 'created_at')
    sort_order = request.args.get('sort_order', 'desc')
    cursor = request.args.get('cursor')

    # Always include id as tiebreaker
    order_clause = f"{sort_by} {sort_order}, id {sort_order}"

    if cursor:
        cursor_data = decode_cursor(cursor)
        op = '<' if sort_order == 'desc' else '>'
        users = db.execute(f"""
            SELECT * FROM users
            WHERE ({sort_by}, id) {op} (%s, %s)
            ORDER BY {order_clause}
            LIMIT %s
        """, [cursor_data['sort_value'], cursor_data['id'], limit + 1])
    else:
        users = db.execute(f"""
            SELECT * FROM users
            ORDER BY {order_clause}
            LIMIT %s
        """, [limit + 1])

    return paginated_response(users)

// Client-specified sorting
app.get('/products', async (req, res) => {
  const allowedSorts = ['name', 'price', 'created_at'];
  const sortBy = allowedSorts.includes(req.query.sort_by) ? req.query.sort_by : 'created_at';
  const order = req.query.order === 'asc' ? 'ASC' : 'DESC';
  const limit = Math.min(req.query.limit || 20, 100);
  const cursor = req.query.cursor;

  const query = cursor
    ? `SELECT * FROM products ORDER BY ${sortBy} ${order}, id ${order} LIMIT $1`
    : `SELECT * FROM products ORDER BY ${sortBy} ${order}, id ${order} LIMIT $1`;

  const products = await db.query(query, [limit + 1]);
  // ... pagination response
});

Common Mistakes

1. No Tiebreaker Column

Equal sort values (same created_at) cause unstable pagination.

2. Allowing Unsafe Sort Columns

Only allow sorting by indexed columns to prevent performance issues.

3. Case-Sensitive String Sorting

Default string sorting is case-sensitive. Use case-insensitive collation.

4. Sorting Before Filtering

Apply filters first, then sort, then paginate.

5. No Sort Direction Validation

Validate that sort_order is 'asc' or 'desc'. Sanitize input.

Practice Questions

1. Why is a tiebreaker column needed in pagination?
2. How do you handle case-insensitive string sorting?
3. Why restrict sortable columns?
4. What is the correct order of operations: filter, sort, paginate?
5. How do you implement multi-column sorting?

Answers:

1. To provide stable ordering when sort values are equal.
2. Use LOWER(column) or case-insensitive collation.
3. Unrestricted sorting on unindexed columns causes full table scans.
4. Apply filters first, then sort, then paginate.
5. Accept comma-separated sort fields and directions.

Challenge: Implement multi-column sorting with pagination. Support sorting by name (asc), then price (desc), then id (asc) as tiebreaker.

FAQ