0

In MySQL I have a table named custom_meta where I am getting the values like

id   meta_value
1    USNEWYORK01
2    USNEWYORK02
3    USNEWYORK03
4    USNEWYORK04
5    USNEWYORK05
6    USNEWYORK06
7    USNEWYORK07
8    USNEWYORK10
9    USNEWYORK14
10   USNEWYORK16
11   USNEWYORK20
12   USNEWYORK21
13   USNEWYORK32
14   USNEWYORK45
15   USNEWYORK56
16   USNEWYORK78
17   USNEWYORK68
18   USNEWYORK69
19   USNEWYORK80
20   USNEWYORK90
21   USNEWYORK99
22   USNEWYORK100
23   USNEWYORK45
24   USNEWYORK101

Now I want to get the meta_value with highest number. So in the table you can see the highest one is USNEWYORK101. So to get that I have made query like this

SELECT meta_value from custom_meta ORDER BY meta_value DESC LIMIT 1

But it is getting USNEWYORK99 always. I have tried to use CAST also but that is also not working.

So can someone help me to get this done? Any suggestions and advice would be really appreciable.

Thanks.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
NewUser
  • 12,713
  • 39
  • 142
  • 236
  • Which version of MySQL are you using? – Kamil Gosciminski Sep 22 '21 at 10:16
  • 1
    Get rid of the substring `USNEWYORK` from the `meta_value` column and cast the remaining part as Integer and sort based on that. I will give you the expected output. – Teja Goud Kandula Sep 22 '21 at 10:17
  • @KamilGosciminski mysql 5.7 – NewUser Sep 22 '21 at 10:18
  • @TejaGoudKandula can you please provide any sample query for that? – NewUser Sep 22 '21 at 10:18
  • You are ordering by a text value so of course it's ordering alphabetically. 9 sorts above 1. You would be better off normalising your data and storing the numerical part separately which you can then sort by. While the solutions offered work, they are not *sargable* so by storing your sort values as a string you will be incurring a performance hit. – Stu Sep 22 '21 at 10:45

3 Answers3

1
SELECT id,
       meta_value,
       Cast(Substring(meta_value, 10, Length(meta_value)) AS UNSIGNED)
FROM   custom_meta
ORDER  BY 3 DESC
LIMIT  1;
Teja Goud Kandula
  • 1,462
  • 13
  • 26
0

For newer versions of MySQL you could use REGEXP_SUBSTR() function, but since you're using 5.7 we could do it some other way.

Assuming that you always have USNEWYORK at the beginning of your values, you could get rid of it, leaving only what comes after that using SUBSTRING() function. Then you need to perform CAST() to convert text to number values so that sorting works properly.

Sample data

create table custom_meta(meta_value varchar(255));

insert into custom_meta(meta_value) values
('USNEWYORK01'),('USNEWYORK02'),('USNEWYORK45'),('USNEWYORK99'),('USNEWYORK101');

Solution

select
    meta_value
from
  custom_meta
order by
  cast(substring(meta_value, 10) as unsigned) desc
limit 1

Output

meta_value 
USNEWYORK101
Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
0

Replace and cast it to int like this:

  1. IF Prefix is FİXED to USNEWYORK:
select * from test 
    order by  CAST(REPLACE(meta_value, "USNEWYORK", "") AS SIGNED) desc;
  1. IF Prefix is not fixed and can be anything , and mysqldb 8.0
select * from test 
    order by  CAST(REGEXP_REPLACE(meta_value, '[^0-9]', '') AS SIGNED) desc;
  1. IF Prefix is not fixed and can be anything , and mysqldb 5.7 You can create this function that explained here and use it like this:
select * from test 
        order by  CAST(STRIP_NON_DIGIT(meta_value) AS SIGNED) desc;
Ali Fidanli
  • 1,342
  • 8
  • 12