CODEWITHSUNDEEP
mysqlsql
Jc John
Jc John

Reputation: 1859

getting maximum value of a varchar column in database

how can i get the maximum value of my column that is a varchar with these values

for example i have a fieldname of myid which is varchar and what i want is to get the maximum value of the myid field . How can i query to get the 1-10 value of myid column?

myid
1-1
1-2
1-3
1-4
1-5
1-6
1-7
1-8
1-10
1-9

Upvotes: 0

Views: 322

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would suggest using this trick:

order by length(myid) desc, myid desc

This will work for the data in the question. A more general answer is:

order by substring_index(myid, '-', 1) + 0, substring_index(myid, '-', -1) + 0

Upvotes: 1

rd_nielsen
rd_nielsen

Reputation: 2459

For the data you've shown:

select myid
from data_table
order by cast(substr(myid, 3, 2) as int) desc
limit 1;

In this case the ordering function is the integer value of the portion of the identifier following the dash. In general--i.e., for different or more complex data--you simply need to determine what the appropriate odering function is.

Upvotes: 1

Related Questions