December 12, 2007 at 7:27 am
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 !!!
December 12, 2007 at 7:53 am
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
December 12, 2007 at 10:47 pm
Waw !!!
This helped me a lot.............:w00t:
Thnks a Lottt.............
u r gr8....:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply