Caso Abierto Español

Field manual · SQL traps

Why does the biggest transfer of the month show up as $990?

If the amount was stored as text, ORDER BY compares it character by character: '9' comes before '75000' because the first character of '990' is bigger, no matter what digits follow.

Where it shows up

Numeric columns imported from a CSV, an API or a form, that arrived as VARCHAR and nobody converted them. Sorting or comparing them as-is gives an order that looks random.

Example

Wrong: sorting a text column as if it were a number

SELECT account, amount
FROM transfers
ORDER BY amount DESC
LIMIT 3;
-- amount is VARCHAR: '990' > '9500' > '870' because it's compared
-- character by character, and the $75,000 transfer never shows up

Right: cast to a number before sorting

SELECT account, amount
FROM transfers
ORDER BY CAST(amount AS BIGINT) DESC
LIMIT 3;
-- TRY_CAST instead of CAST if any row might carry non-numeric text
-- (a thousands comma, a blank cell): TRY_CAST gives NULL instead of blowing up the query

A VARCHAR sorts as text: it compares the first character, and only checks the second if they tie. '990' beats '75000' because '9' > '7', and the comparison doesn't care that '75000' has more digits left over. CAST(amount AS BIGINT) converts before sorting, and there 75000 really is bigger than 990. The difference between CAST and TRY_CAST matters on real data: CAST blows up the whole query if a single row carries '12,000' with a comma or an empty cell; TRY_CAST turns that one row into NULL and lets the rest through. Which one to use depends on whether you want to find out about the dirty data now or later.