Search code examples
mysqlsqlmaxvarchar

Getting the Max values from a varchar field

I have a varchar field as my Employee ID and it is formated like this:

Format: YYYY-DateHired-Number Example: 2012-1203-0001

But Im having hard time in inserting new record because to insert a new incremented ID, I need to get the latest value in this field before I increment it.

How can I get the row with the latest/highest (MAX) Numberin the most recent year?

Thanks in advance :)

Solution

  • Assuming that your EmployeeID's format is YYYY-MMdd-XXXX. Try this,

    SELECT MAX(EmployeeID)
FROM tableName
WHERE EmployeeID LIKE CONCAT(SUBSTRING(EmployeeID, 1, 4),'%')

    SQLFiddle Demo