Best way to find max serial num in MySQL database

0
votes

I have a MySQL database with a column named ID(VARCHAR 60)

The data of ID is linked by id_num + serial num.

For example, a group data is below

  • 367618648+0001
  • 367618648+0002
  • 51687687+0001
  • 51687687+0002
  • 51687687+0003
  • 51687687+0004

I want to know the best way to find the max "serial num" for an "id_num"?

1
mysqlsql
Is 367618648+0001 all in the id field? – JNevill
That's poor schema design. It should really be two separate columns. – Joel Coehoorn
Yes. I know the schema is bad ,but this is a old database with large data and that's why i am asking how to solve this:). – William

1 Answers

0
votes

Use substring_index(). The following gets the max for all id_nums:

select substring_index(id, '+', 1) as id_num,
       max(substring_index(id, '+', -1) + 0) as serial_num
from t
group by id_num;