Search Postgresql Archives

Re: Select Cast Error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux