Find max value in sql table

Question

I need to retrieve an sql MAX result from a table with a varchar column like this "MYNAME-0xxxxx" or "MYNAME-xxxxx" (a fixed string followed by a minus followed by 6 digits starting with zero OR 5 digits starting by a non zero)

I tried this:

SELECT MAX(  substring( myColumn  , locate('-', myColumn) ) )  
FROM MyTable

With this statement I am able to achieve the maximum number of all column value in this form "MYNAME-xxxx" but it does not apply on the columns like "MYNAME-0xxxxx)

let'say we have

JOHN-12345
JOHN-12346
JOHN-012347

I want my result be "012347" but my code return me "12346". Seems to me that MAX value between 12346 and 012347 in sql is 12346. Please note that number are incremented so if we start from 012347 we can reach 999999

Answer 1

I cannot comment yet, so I'll give my hint here:

I think the problem is you are comparing strings. In this case, SQL looks for the alphabetical order, a 1 is higher than 0, so the string "0999" is lower than the string "1". Think of it as words, the word "b" will be ordered before the word "zaaa".

Convert the string you're trying to compare into a number and try again.

I hope I could explain myself.

Answer 2

try this

select max(id) from(
SELECT namecol REGEXP '[[:digit:]]' as id FROM table) d

Answer 3

Try this one

SELECT val,
       Cast(Substring_index(val, '-', -1) AS UNSIGNED) AS valOrder
FROM   temp
ORDER  BY valorder DESC
LIMIT  1  

or this one.

SELECT val
FROM   temp
ORDER  BY Cast(Substring_index(val, '-', -1) AS UNSIGNED) DESC
LIMIT  1;

Online Demo: http://www.sqlfiddle.com/#!9/dda898/22/0

---

<sup>Further readings
- SUBSTRING_INDEX() Function - link 01, link 02, link 03
- UNSIGNED and SIGNED in MySQL - link 01, link 02</sup>

Answer 4

SELECT MAX(column_name)
FROM   table_name
WHERE  CONDITION;

Answer 5

Although the other answers will work for the database as-is, I'd strongly suggest storing the string portion of the data value and the numeric portion in separate fields in the table. This will allow you to give the numeric portion an integer type, and therefore be able to find the maximum correctly without worrying about parsing the value at runtime. You could then use the following query to get the maximum:

-- id field becomes separate id_name (varchar) and id_num (int) fields
SELECT CONCAT(id_name, '-', id_num) AS id
FROM MyTable
ORDER BY id_num DESC
LIMIT 1

This is better in terms of query optimisation as well, because it means that an index on id_num could make the ordering process trivial and save a lot of time if required. Any kind of aggregate function (such as MAX) over a portion of a string is going to require a (very slow) table scan because every string must be parsed into its component pieces before we execute the MAX function. With an indexed list of numbers, the list is virtually already sorted, so all the query optimiser has to do is jump to the end of the list to get the MAX.

I have implemented it with a ORDER BY and LIMIT 1 above because I assumed you also want the string portion of the ID in your results, which MAX would not give you. If you only need the numeric portion and can throw away the string, this query will also work:

SELECT MAX(id_num) FROM MyTable

Answer 6

To get the maximum value of a numeric column use the MAX() function.

SELECT MAX(<numeric column>) FROM <table>;

SELECT MAX(<numeric column>) FROM <table> GROUP BY <other column>;