Post reply

TSQL PRoblm

  • August 24, 2006 at 3:02 pm

    #656724

    Now he's got one more .

  • August 25, 2006 at 2:10 am

    #656780

    Tadaa!!!!

     

    SELECT   z.part1 + '-' + CONVERT(VARCHAR, z.part2) refNo

    FROM     (

                 SELECT   PARSENAME(REPLACE(refNo, '-', '.'), 2) part1,

                          MAX(CONVERT(INT, PARSENAME(REPLACE(refNo, '-', '.'), 1))) part2

                 FROM     YourTable

                 GROUP BY PARSENAME(REPLACE(refNo, '-', '.'), 2)

             ) z

    ORDER BY 1

    N 56°04'39.16"
    E 12°55'05.25"

  • August 25, 2006 at 3:14 am

    #656786

    Darn, you beat me to the parsename method.

    (just increasing my postcount)

    /Kenneth

  • August 25, 2006 at 7:24 am

    #656819

    The only problem with that solution is that if a number is written like this abc012, your query will return abc12.

     

    That's why I wrote mine that way... might not be necessary but just in case .  Also now I rechecked my version against Eric's and mine is faster with the added index (since Eric didn't provide one )

  • August 25, 2006 at 8:33 am

    #656840

    I say we keep this thread going until we've got, say, 6 more ways of solving this problem - OR - until San replies. Whichever comes first.

    :o)

  • August 25, 2006 at 8:38 am

    #656844

    That'll take too long.. The longest threads here have gone far in the 100s so I got other stuff to do .

  • August 25, 2006 at 10:02 am

    #656862

    I considered the same solution Peter L, but encountered the problem of lopping off leading zeros. That's actually a common mistake that is included in report tester scripts. These aren't even leading zeros we're looking at, so it really is unacceptable.

    --Eric Swanson
    http://www.ericis.com/

    Posts++;

  • August 25, 2006 at 10:14 am

    #656865

    Why limit it to a SQL 2000 solution ?

    Create Table #t ( RefNo Varchar(20) not null)

    insert #t

    select 'ABC-0' union all

    select 'ABC-1' union all

    select 'XYZ-0' union all

    select 'XYZ-1' union all

    select 'XYZ-02' union all

    select 'PQR-0' union all

    select 'MNO-0' union all

    select 'MNO-1' union all

    select 'SPX-0' union all

    select 'SPX-1'

    WITH RefNoCTE (RefNo,AlphaRef,NumRef)

    AS

    (

      Select RefNo,

      Left(RefNo, Dash-1) As AlphaRef,

      Cast(Substring(RefNo, Dash+1, DataLength(RefNo)) As int) As NumRef

      From (

        Select RefNo, CharIndex('-', RefNo) As Dash

        From #t

      ) dt

    )

    Select RefNo

    From RefNoCTE As r

    Inner Join

    (

      Select AlphaRef, Max(NumRef) As MaxNum

      From RefNoCTE

      Group By AlphaRef

    ) dt

      On (dt.AlphaRef = r.AlphaRef And

          dt.MaxNum = r.NumRef)

  • August 25, 2006 at 10:57 am

    #656875

    How about someone provides some oracle solutions while I come up with the Access 2K version?

  • August 25, 2006 at 11:00 am

    #656877

    Don't make me fire up my old BTrieve workstation ...

  • August 25, 2006 at 11:02 am

    #656879

    Damn, I'm still to green to understand that joke .

  • August 25, 2006 at 12:23 pm

    #656904

    This is the 'Post of the Week' !!!

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • August 25, 2006 at 12:27 pm

    #656906

    Well, I think everyone likes a little challenge.

    Personally, I like a little champagne. Right about now. It's Friday, after all.

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply