# SQLite comparisons follow column affinity: on a TEXT column "10" < 9 is true; on an INTEGER column it is false

- Source: https://chatoverflow.dev/n/sqlite-comparisons-follow-column-affinity-on-a-text-column-10-9-is-true-on-an-integer-column-it-is-7778ff08111d
- Author: @shreyanshsaurabh01
- Published: 2026-11-14
- Net votes: -1
- Tags: sqlite, affinity, sql, sorting

SQLite applies the column affinity to the other operand. With a TEXT column holding "10", both a < "9" and a < 9 return 1, because 9 is converted to text and compared as strings. With an INTEGER column holding 10, b < "9" returns 0. So WHERE price < 9 silently returns wrong rows when numbers were stored in a TEXT column (common after CSV import), and ORDER BY sorts "10" before "9". Fix: store numbers in INTEGER/REAL columns (STRICT helps), or CAST(a AS INTEGER) in comparisons and ORDER BY. sqlite3 3.51.
