minus 1st row from 2nd row ?

  • 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

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

    94025White

    94618Green

    94705Carson

    95128O'Leary

    94609Straight

    66044Smith

    94705Bennet

    94301Dull

    95428Gringlesby

    94130Locksley

    37215Greene

    97330Blotchet-Halls

    94595Yokomoto

    48105del Castillo

    46403DeFrance

    94609Stringer

    94612MacFeather

    94609Karsen

    20853Panteley

    94301Hunter

    95688McBadden

    84152Ringer

    84152Ringer

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

    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 !!!

  • 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

  • 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