Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: to get the max value

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Chennai, INDIA
    Posts
    3

    Re: to get the max value

    Originally posted by diya
    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

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: to get the max value

    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

    Originally posted by diya
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •