On fim, 2006-09-21 at 21:57 -0400, Brad Budge wrote: > I have a field that is varchar(15) type and an example of data I'm > working with is (PROJ-0001-06) > select cast((max(substring(test.test from 6 for 4))) as integer) + 1 > FROM test where max(substring(test.test from 11 for 2)); > as someone already pointed out, this where clause is just equivalent to WHERE '06' and does not mean anything > List below is a better idea of what my table looks like and the result > I need. > PROJ-0004-05 > PROJ-0001-06 > PROJ-0002-06 > PROJ-0003-06 > When I run my select statement I want to return the number 4. The > idea is that I need the next highest number in the middle but take in > consideration that the highest trailing numbers take president. if all the values are really formatted like this, you could ORDER BY to get the value you want: SELECT 1 + CAST(substring(test.test from 6 for 4) AS INTEGER) FROM test ORDER BY substring(test.test from 11 for 2) DESC, substring(test.test from 6 for 4) DESC LIMIT 1; gnari
