CODEWITHSUNDEEP
mysqlsqlmaxvarchar
user1687525
user1687525

Reputation: 33

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 :)

Upvotes: 3

Views: 1121

Answers (2)

Manoj De Mel
Manoj De Mel

Reputation: 995

Hope this helps too:

SELECT * FROM tableName
WHERE EmployeeID IN (SELECT MAX(EmployeeID) FROM tableName)

http://sqlfiddle.com/#!2/2d100/14

Upvotes: 2

John Woo
John Woo

Reputation: 263723

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

Upvotes: 2

Related Questions