hi i have a ID column of varchar(10).

I store values in the format say 1001/04-05. In this the number left to the slash / represents just a slno that should be incremented for each record. and the nos to the right of slash represent the year 04-05

from this column i want to get the max slno.leaving the yr.

i can get the left side sl no but don't know how to get the max of them.
i used

Select left(id,4) from table1.
pls help
thanks

Hi,

I don't understand how do you store the value, i have tried but shows some other value.

SQL> create table sereal(slno varchar(20));

Table created.

SQL> insert into sereal values(1000/04-05);

1 row created.

SQL> insert into sereal values(1001/04-05);

1 row created.

SQL> select * from sereal;

SLNO
--------------------
245
245.25

SQL>


Regards
Syed

I strongly recommend redesigning your table to be in first normal form.

To obtain the solution for your current table structure,

Oracle 9i:
Select MAX(SUBSTR(id, 1, 4)) from table;

SQL Server 2000:
Select MAX(LEFT(id, 4)) from table;

Hi Diya,
It seems that you slno may keep increasing day by day.
So use MAX, INSTR and SUBTSR function. This will help you
to meet your requirement.

Thanx
praveenpr@rediffmail.com
Praveen