Why is MAX() not showing the highest number?

Question

I am having an issue with MAX() on postgres.

With the following data set:

Batch Number
1
2
3

When I query this:

SELECT "Batch Number" FROM tbl WHERE "Batch Number" = (select max("Batch Number") from tbl)

I get the correct value (3)

But when I manually insert a batch number of 10000 and run the same query it still shows 3 as the highest number when it should be 10000

Batch Number
1
2
3
10000

user_87189 · Answer

If you're sorting numerically you absolutely need an integer field otherwise it's done ASCIIabetically, as in by first letter, then second letter.

This means your sort order is like in a dictionary, as in:

Where only the first digit matters unless there's a tie. Look at the output of SELECT * FROM ... ORDER BY on that column.

Note that column names with spaces in them are super annoying and should be avoided. Just use an underscore, or like batchno instead.

user_1144035 · Answer

I'm not sure why you are using a subquery for this. You want a numeric sort, so you can cast. I would suggest:

SELECT "Batch Number"
FROM tbl
ORDER BY "Batch Number"::numeric DESC
LIMIT 1;

user_4991516 · Answer

You can use oracle/plsql

SELECT TO_CHAR(MAX(TO_NUMBER(Batch_Number)))
FROM tbl