query one row backward - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query one row backward

Author

Topic

yuan22m
Starting Member

3 Posts

Posted - 2008-07-08 : 20:33:14

Hi i have a database that somehow looks like this

ID Point
1 10.5
2 9
3 12.4
.
.
.

how will i query the records so i can get the diff from previous row to the current row in one query. So i will have a column name diff. If im in the second row i will have the difference of 9 and 10.5.
The first row would then have 0.

I hope i explain it well.

Thanks a lot..

Yuan

yuan22m
Starting Member

3 Posts

Posted - 2008-07-08 : 20:34:38

By the way im using SQL server 2005.

Thanks.

Yuan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 20:42:24

Is this what you want ?

DECLARE @TABLE TABLE
(
	ID 	int,
	Point	decimal(5,2)
)
INSERT INTO @TABLE
SELECT	1, 10.5 UNION ALL
SELECT	2,  9   UNION ALL
SELECT	3, 12.4 UNION ALL
SELECT  5, 11.3

;WITH data (ID, Point, row_no)
AS
(
	SELECT	ID, Point, row_no = row_number() OVER (ORDER BY ID)
	FROM	@TABLE
)
SELECT	c.ID, c.Point, diff = c.Point - p.Point
FROM	data c
	left JOIN data p	ON	c.row_no	= p.row_no + 1

/*
ID          Point   diff     
----------- ------- -------- 
          1   10.50 NULL
          2    9.00    -1.50 
          3   12.40     3.40 
          5   11.30    -1.10 

(4 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 23:34:10

Another way using APPLY:-

SELECT t.ID,t.Point,t.Point-COALESCE(b.Point,0) AS Diff
FROM Table t
OUTER APPLY (SELECT TOP 1 Point
             FROM Table
             WHERE ID<t.ID
             ORDER BY ID DESC)b

yuan22m
Starting Member

3 Posts

Posted - 2008-07-29 : 21:21:43

thanks a lot..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources