CODEWITHSUNDEEP
mysqlsql
wadapav
wadapav

Reputation: 107

Mysql - how to get the max value of a varchar column

I have a table as follows ID is a numeric column and Total is Varchar(20) - table name is orders:

ID       Total

1        $10

2        $1,000

3        $1500.50

4        300

5        20.50

6        -80

through a 

SELECT statement I would like to get the Id of the highest total i.e 3. 

SELECT ID, MAX(CONVERT(Total, SIGNED INTEGER)) AS total FROM orders

gives me the highest total but incorrect ID.

I would like to ignore the dollar sign ($) and comma (,).

Upvotes: 1

Views: 7627

Answers (3)

user2745280
user2745280

Reputation: 21

Your query should be like this. It works, I have used it too.

Try this:

SELECT MAX(CONVERT({column_name}, SIGNED INTEGER)) AS {column_name or xyz_name} from {table_name}

Upvotes: 2

mvp
mvp

Reputation: 116187

Storing money amounts in VARCHAR field sounds like asking for trouble.

You can try to remove leading $ using REPLACE before converting to number, but it is still wrong thing to do.

Please rework your schema and store money amounts in appropriately typed columns, and these problems will not arise.

Upvotes: 1

GKV
GKV

Reputation: 501

just use MAX(CONVERT(replace(replace(total,',',''),'$',''), SIGNED INTEGER)) AS total

Upvotes: 3

Related Questions