Hi,

If you can find a UDF "FIND" you coul do something like this (if you
can't or don't want to add an extra column)

SELECT VC_BILLNO
FROM PURC_BILL_MAST
ORDER BY
CAST(SUBSTRING(VC_BILLNO FROM 1 FOR FIND('/', VC_BILLNO) - 1) AS
INTEGER)

Cheers!


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:36 PM 26/05/2005 +0000, you wrote:
> >Hi experts,
> >I am doing an inventory s/w using Firebird.I have tried the following
> >query to select the First record which satisfies the criteria
> >
> > Select COALESCE(MIN(VC_BILLNO),'NULL') CODE from PURC_BILL_MAST
> > where NN_YEAR = 2005
> >
> >but i am not getting the needed answer..
> >Eg:in my table the values of vc_billno are like this
> >
> >10/2005-2006
> >2/2005-2006
> >3/2005/2006
> >9/2005-2006
> >
> >and i want to get the result 2/2005-2006 when using the "MIN function"
> >But notgetting the right one.
> >NB:vc_billno is declared as varchar type
> >
> >Hope u understand the problem, i am new in firebird..i expect ur
> >valuable sugestions..
>
> Your problem here is that the "minimum" value of an alphanumeric

character

> is determined by the value of the character code, not the value that

the

> character represents. There is nothing (in SQL) that you could do

to make

> 2/2005-2006 be evaluated as "of lowest value" in this set of

strings. The

> alpha character '1' is the lowest. '10' is a string, not a number.
>
> I suggest that you add two extra columns (smallint) and write a Before
> Insert Or Update trigger to extract the MONTH and YEAR

(respectively) from

> whatever date value was used to assemble the vc_billno and plug them

into

> those columns.
>
> ./heLen