Find the second newest row with ORDER BY and OFFSET

By Batu · English technical notes

Find the second newest row with ORDER BY and OFFSET

To find the second newest row in SQLite, sort newest first and use LIMIT 1 OFFSET 1: ORDER BY created_at DESC LIMIT 1 OFFSET 1. MAX(created_at) answers a different question: it returns the newest date, as the first result in this experiment shows.

The in-memory table contains exactly three distinct ISO-8601 date strings: 2024-01-03, 2024-01-02, and 2024-01-01. Printing the schema and seed values makes the ordering assumption visible. MAX returns 2024-01-03; the corrected query skips that first descending row and returns 2024-01-02. Each expected value is asserted.

SQLite documents that LIMIT bounds the result rows and OFFSET omits the first M rows before returning the next N. The explicit OFFSET form avoids the less-readable comma form of LIMIT. This example uses stable Python sqlite3 methods and needs no newer Python version.

“Second newest” needs a business rule when timestamps tie. This fixture has unique dates, so one offset identifies one row. With duplicate timestamps, decide whether you want the second physical row or the second distinct timestamp; the latter needs a different query such as a DISTINCT subquery. Also add a deterministic secondary ordering column when selecting among ties.

Source: SQLite SELECT and LIMIT/OFFSET.

AI assistance disclosure: This article was drafted with AI assistance and verified against the cited documentation and a synthetic in-memory example.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE event (created_at TEXT NOT NULL)")
seed_rows = [("2024-01-03",), ("2024-01-02",), ("2024-01-01",)]
con.executemany("INSERT INTO event (created_at) VALUES (?)", seed_rows)

newest = con.execute("SELECT MAX(created_at) FROM event").fetchone()[0]
second_newest = con.execute(
    "SELECT created_at FROM event "
    "ORDER BY created_at DESC LIMIT 1 OFFSET 1"
).fetchone()[0]

print("schema: CREATE TABLE event (created_at TEXT NOT NULL)")
print("seed rows:", seed_rows)
print("MAX:", newest)
print("second newest:", second_newest)

assert newest == "2024-01-03"
assert second_newest == "2024-01-02"
con.close()

schema: CREATE TABLE event (created_at TEXT NOT NULL)
seed rows: [('2024-01-03',), ('2024-01-02',), ('2024-01-01',)]
MAX: 2024-01-03
second newest: 2024-01-02