mysqlmax

getting maximum value for varchar column mysql

i need to get the largest number in column "meta_value" while other column equals "utteranceId", and i get wrong value.

i tried to use the max function but i don't get the largest number.

the meta_value column is of type varchar, and there is more records in the table that are text, but the records of meta_key: "utteranceId" are all numbers.

table:

meta_id post_id meta_key    meta_value
    986     168 utteranceId          1
    987     169 utteranceId          2
    990     170 utteranceId          3
    993     171 utteranceId          4
    996     172 utteranceId          5
    999     173 utteranceId          6
   1002     174 utteranceId          7
   1005     175 utteranceId          8
   1008     176 utteranceId          9
   1011     177 utteranceId         10

the query i use:

SELECT post_id, max(meta_value) FROM wp_postmeta where meta_key = "utteranceId";

result: 168 9

i need to get: 177 10

i

Solution

  • To handle varchar as number:

    SELECT MAX(CONVERT(meta_value, SIGNED)) FROM wp_postmeta where meta_key = "utteranceId";

    To select the corresponding post_id:

    SELECT post_id, meta_value FROM wp_postmeta
WHERE CONVERT(meta_value, SIGNED) = (
    SELECT MAX(CONVERT(meta_value, SIGNED))
    FROM wp_postmeta 
    WHERE meta_key = "utteranceId" )
AND meta_key = "utteranceId";