minus 1st row from 2nd row ?

Question

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

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

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')

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: 2423 More actions · Answer 2

Waw !!!

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

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

u r gr8....:)