CODEWITHSUNDEEP
mysqlsqldatabase
Jc John
Jc John

Reputation: 1859

how to get the maximum value of a varchar column and auto increment it

i want to get the maximum value of my varchar column which must be unique thats why i want to get the max value and increment it.

For example i have this situation from tbl_check:

+---------------+
|  check_up_id  |
-----------------
|      1-0      |
|      1-2      |
|      11-1     |
|      111-3    |
|      12-4     |
|      52-5     |
|      1-6      |
|      1-7      |
|      3-9      |
|      3-8      |
|      3-11     |
|      3-10     |
+---------------+

in my check_up_id considered that i have incremented the right side ( 1 - **) of my id which is not in order. Now what i wan't is how can i query to get the maximum value of the right side. Only right side to be considered, in that situation that i have sometimes 3 digits or 2 digits or sometimes only one digit in the left side. What i want is to return the largest value of the right side. In my example i want to return the 3-11 in my query. Disregard the incremental because i will do it in php side. What i want is to return the largest value of the right side after the ( - )

Upvotes: 0

Views: 555

Answers (1)

Barmar
Barmar

Reputation: 781944

Use:

SELECT MAX(CAST(SUBSTRING_INDEX(check_up_id, '-', -1) AS DECIMAL))
FROM tbl_check

SUBSTRING_INDEX returns the substring of a column containing the specified number of fields delimited by the - character. The negative count means to count from the right, so this returns the rightmost field.

To get the whole field that contains this, use it in a JOIN:

SELECT check_up_id
FROM tbl_check
JOIN (
    SELECT MAX(CAST(SUBSTRING_INDEX(check_up_id, '-', -1) AS DECIMAL)) as max_right
    FROM tbl_check) AS x
ON SUBSTRING_INDEX(check_up_id, '-', -1) = max_right

Upvotes: 2

Related Questions