query select max with mixed string and int

Question

I have a table which contain a column for generated code, the data type is VARCHAR with mixed string/int values like :

Table demo
ID        code
==============
1      |  001qwe
2      |  002qwe
3      |  001asd

Question :

1. How to get max value that contain qwe or asd, i want it used as filter.

2. How to get id of row which contain the maxed value

i want something like :

select *,MAX(SUBSTRING(code, 1, 3)) from demo where SUBSTRING(code, 4, 3) = 'asd'

Answer 1

Yes this case code length is 6 and number is 3 digit in the beginning of data

Considering above you can write your query as below

select *,left(`code`,3)
from demo
order by left(`code`,3) * 1 desc
limit 1

DEMO

to get individual results you can use following

SELECT a.id qweid, a.code qwecode,b.id asdid,b.code asdcode
FROM 
(SELECT id,`code`
FROM demo
WHERE RIGHT(`code`,3) = 'qwe'
ORDER BY LEFT(`code`,3) * 1 DESC
LIMIT 1) a
CROSS JOIN(SELECT id,`code`
FROM demo
WHERE RIGHT(`code`,3) = 'asd'
ORDER BY LEFT(`code`,3) * 1 DESC
LIMIT 1) b

DEMO