sorting SQL Origin: Data OSI layer 7 · Application Last updated 18 Nov 2026

Numbers sort wrongly (1, 10, 2): VARCHAR column sorted as text

A numeric-looking column stored as text sorts alphabetically, so "10" comes before "2".

Exact message you may see numbers sorted as text 1 10 2ORDER BY varchar number sorts incorrectlysorting numbers stored as VARCHAR'10' < '9' string comparison SQLnatural sort numbers SQL
Seen on: SQL MySQL PostgreSQL

Meaning

Text comparison is character by character. Store numbers in numeric columns, or cast in the query (ORDER BY CAST(col AS INTEGER)), and prefer natural-sort expressions/zero padding only as a stopgap. Same issue affects comparisons: '10' < '9'.

Common causes

  • Numeric data in VARCHAR/TEXT column
  • CSV import without types
  • Version strings sorted as text
  • Mixed numeric and non-numeric values
  • Collation affecting order

⚡ Quick fix

  1. Change the column type to INT/DECIMAL
  2. ORDER BY CAST(col AS INT) as a workaround
  3. Clean non-numeric values before converting

Detailed fix by platform

SQL

  1. SELECT * FROM items ORDER BY CAST(sku_number AS INTEGER);

How to diagnose

  1. Column type — Text?
  2. Values — Numeric only?
  3. Cast — Safe?

🔧 Still not fixed?

Many errors look alike. If the steps above didn’t solve it, one of these is probably what you’re facing:

Where this error sits

Origin: Data. Errors that are often confused with or caused by this one (click to open):

22P02 — ERROR: invalid input syntax for type integer: "abc" (PostgreSQL)22P028114 — SQL Server Msg 8114: Error converting data type varchar to numeric (SQL)8114cartesian — Query returns far too many rows: accidental Cartesian product (missing JOIN condition) (SQL)cartesianCASE — CASE expression returns mixed types: conversion failed / types cannot be matched (SQL)CASECOUNT — COUNT(column) ignores NULLs: counts differ from COUNT(*) (SQL)COUNTGROUP_CONCAT — GROUP_CONCAT / STRING_AGG result truncated or fails (group_concat_max_len, string length limit) (SQL)GROUP_CONCATimplicit conversion — Query slow because of an implicit type conversion or function on the column (index not used) (SQL)implicit conversioninteger division — 5 / 2 = 2: integer division silently truncates the result (SQL)integer divisionsorting

Explore the map of this error Full error map →

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.

💬 What worked for others

No tips yet — did something else fix this for you?

Share what worked for you

Tips are checked by an editor before they appear. Links are removed.