minus 1st row from 2nd row ?

  • U J

    SSCrazy

    Points: 2420

    Hi Guys,

    I want to minus 1st row from 2nd row ...

    how can achieve this..?

    e.g.

    i hv this authors table which looks like..

    -------------------

    zip au_lname

    -------------------

    94025 White

    94618 Green

    94705 Carson

    95128 O'Leary

    94609 Straight

    66044 Smith

    94705 Bennet

    94301 Dull

    95428 Gringlesby

    94130 Locksley

    37215 Greene

    97330 Blotchet-Halls

    94595 Yokomoto

    48105 del Castillo

    46403 DeFrance

    94609 Stringer

    94612 MacFeather

    94609 Karsen

    20853 Panteley

    94301 Hunter

    95688 McBadden

    84152 Ringer

    84152 Ringer

    -------------------

    now i want to minus 94025 from 94618 , 94618 from 94705, 94705 from 95128,95128 from 94609 and so on....

    in short...I want to minus 1st row from 2nd row in whole table..

    Thnks for any Help !!!

  • Roy Rogers

    SSCertifiable

    Points: 7712

    Unless you have a row number or a way to sort the values to get this result set I don't think it is possible.

    If you have a row number

    DECLARE @MyTable TABLE

    (

    RowNum INT IDENTITY,

    zip INT,

    au_lname VARCHAR(100)

    )

    SET NOCOUNT ON

    INSERT @MyTable (zip, au_lname) VALUES (94025, 'White')

    INSERT @MyTable (zip, au_lname) VALUES (94618, 'Green')

    INSERT @MyTable (zip, au_lname) VALUES (94705, 'Carson')

    INSERT @MyTable (zip, au_lname) VALUES (95128, 'O''Leary')

    INSERT @MyTable (zip, au_lname) VALUES (94609, 'Straight')

    INSERT @MyTable (zip, au_lname) VALUES (66044, 'Smith')

    INSERT @MyTable (zip, au_lname) VALUES (94705, 'Bennet')

    INSERT @MyTable (zip, au_lname) VALUES (94301, 'Dull')

    INSERT @MyTable (zip, au_lname) VALUES (95428, 'Gringlesby')

    INSERT @MyTable (zip, au_lname) VALUES (94130, 'Locksley')

    INSERT @MyTable (zip, au_lname) VALUES (37215, 'Greene')

    INSERT @MyTable (zip, au_lname) VALUES (97330, 'Blotchet-Halls')

    INSERT @MyTable (zip, au_lname) VALUES (94595, 'Yokomoto')

    INSERT @MyTable (zip, au_lname) VALUES (48105, 'del Castillo')

    INSERT @MyTable (zip, au_lname) VALUES (46403, 'DeFrance')

    INSERT @MyTable (zip, au_lname) VALUES (94609, 'Stringer')

    INSERT @MyTable (zip, au_lname) VALUES (94612, 'MacFeather')

    INSERT @MyTable (zip, au_lname) VALUES (94609, 'Karsen')

    INSERT @MyTable (zip, au_lname) VALUES (20853, 'Panteley')

    INSERT @MyTable (zip, au_lname) VALUES (94301, 'Hunter')

    INSERT @MyTable (zip, au_lname) VALUES (95688, 'McBadden')

    INSERT @MyTable (zip, au_lname) VALUES (84152, 'Ringer')

    INSERT @MyTable (zip, au_lname) VALUES (84152, 'Ringer')

    SELECT A.zip, B.zip, B.zip - A.zip Value, A.au_lname

    FROM

    @MyTable A

    JOIN

    @MyTable B

    ON

    A.RowNum = B.RowNum - 1

    Regards,
    gova

  • U J

    SSCrazy

    Points: 2420

    Waw !!!

    This helped me a lot.............:w00t:

    Thnks a Lottt.............

    u r gr8....:)

Viewing 3 posts - 1 through 3 (of 3 total)

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