Data in table :LR_Master

BR1HYD1

BR1HYD2

BR1HYD3

BR1HYD18

BR2SEC1

BR2SEC2

BR2SEC3

now the query i write gives correct max record if more then 10 (select max(lrbranch) from LR_Master where lrbranch like 'BR1HYD[0-1000000]%')

wrong if select max(lrbranch) from LR_Master where lrbranch like 'BR1HYD%'

second instanct

select max(lrbranch) as ret from LR_Master where lrbranch like 'BR2SEC[1%-1000000%]%' it does't retrive max but

select max(lrbranch) as ret from LR_Master where lrbranch like 'BR2SEC%' retrives correct if less than 10

i need a single statement which get correct max number along with BR1HYD or BR2SEC

BR1HYD should return BR1HYD18

BR2SEC should return BR2SEC3

Requirement is only one single SQL statement not Stored procedure.  please help, it's very urgent.

regards..