CODEWITHSUNDEEP
sqlpostgresql
phema
phema

Reputation: 69

Why is MAX() not showing the highest number?

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

Upvotes: 3

Views: 945

Answers (3)

Lahiru Gamage
Lahiru Gamage

Reputation: 883

You can use oracle/plsql

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 2

tadman
tadman

Reputation: 211600

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:

1
10000
2
20000
3
90000

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.

Upvotes: 5

Related Questions