CODEWITHSUNDEEP
mysqlsql
hrishi
hrishi

Reputation: 1656

Select max number group by initial letter in MySQL

I have column say, Process in MySQL table with values like A1,A2,..A100, B5,B7..B700, C2,C3...C900 each in separate row. I want to find max number from each initial. so output will be as

A100, B700,C900

I am using below query 

select MAX(process) as max_process
from tablename
group by substr(process,1,1)

but it don't gives me max number

Upvotes: 0

Views: 169

Answers (3)

forpas
forpas

Reputation: 164174

You get the 1st char of process with left(process, 1) and the numeric part with substring(process, 2).
Then by simply adding 0 to the numeric part you convert it to a number:

select
  left(process, 1) process1, 
  max(substring(process, 2) + 0) max_process
from tablename
group by process1

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37470

Try:

select prefix, max(number) `max` from (
  select substring(process, 1, 1) prefix,
         cast(substring(process, 2, 100) as signed) number
  from MyTable
) a group by prefix;

Demo

Upvotes: 2

Fahmi
Fahmi

Reputation: 37483

You can try below -

select MAX(cast(substr(process,2,length(process)-1) as signed)) as max_process
from tablename
group by substr(process,1,1)

Upvotes: 2

Related Questions