June 7, 2016 at 9:19 am
How to get a value from previous result row of a SELECT statement. I would like to use view.
If we have a table called cardevent and has rows [ID(int) , Value(Money) ] and we have some rows in it, for example
ID --Value
1------70
1------90
2------100
2------150
2------300
3------150
3------200
3-----250
3-----280
so on...
How to make one Query that get each row ID,Value and the previous Row Value in which data appear as follow
ID --- Value ---Prev_Value
1 ----- 70 ---------- 0
1 ----- 90 ---------- 70
2 ----- 100 -------- 90
2 ------150 -------- 100
2 ------300 -------- 150
3 ----- 150 -------- 300
3 ----- 200 -------- 150
3 ---- 250 -------- 200
3 ---- 280 -------- 250
so on.
So can anyone help me to get the best solution for such a problem ?
June 7, 2016 at 9:25 am
To know what the previous row is, you need a column (or combination of columns) by which to sort. A table has no inherent order, so which column(s) will you be using? Once you've got that, if you're really on SQL Server 2008, you can do a self-join on RowNumber = RowNumber + 1. If you have SQL Server 2012 or later, you can use the LAG function.
John
June 7, 2016 at 9:34 am
That's correct. there is no order I am using view and I have to find out this in subquery inside the view.
June 7, 2016 at 9:42 am
Here's the example for 2008 and 2012.
DECLARE @Sample TABLE(
ID int,
Value money)
INSERT INTO @Sample
VALUES
(1, 70 ),
(1, 90 ),
(2, 100),
(2, 150);
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY ID, Value) rn
FROM @Sample
)
SELECT a.ID, a.Value, ISNULL(b.Value, 0)
FROM CTE a
LEFT
JOIN CTE b ON a.rn = b.rn + 1;
SELECT *, LAG(Value, 1, 0) OVER( ORDER BY ID, Value)
FROM @Sample;
June 7, 2016 at 9:46 am
Luis has shown what's the easiest way for 2008. Use a CTE that inserts a row number to order the data and determine what the previous row is.
I'd also suggest you use some DDL When you ask a question. Makes it easy for everyone to see what data there is and mock this, or make changes if you need to alter your requirements.
CREATE TABLE MyID
( id INT
, idvalue INT
);
GO
INSERT MyID
VALUES (1, 70 ),
(1, 90 ),
(2, 100),
(2, 150),
(2, 300),
(3, 150),
(3, 200),
(3, 250),
(3, 280);
GO
-- check data
SELECT * FROM Myid;
GO
For results, I prefer you build a quick test like this:
-- write test
EXEC tsqlt.NewTestClass
@ClassName = N'WindowTests';
go
CREATE PROCEDURE [WindowTests].[test check the previous row value for MyID]
AS
BEGIN
-- assemble
CREATE TABLE #expected (id INT, myvalue INT, PrevValue int)
INSERT #expected
VALUES (1, 70 , 0 ),
(1, 90 , 70 ),
(2, 100 , 90 ),
(2, 150 , 100),
(2, 300 , 150),
(3, 150 , 300),
(3, 200 , 150),
(3, 250 , 200),
(3, 280 , 250)
SELECT *
INTO #actual
FROM #expected AS e
WHERE 1 = 0
-- act
INSERT #actual
EXEC dummyquery;
-- assert
EXEC tsqlt.AssertEqualsTable
@Expected = N'#expected'
, @Actual = N'#actual'
, @FailMsg = N'Incorrect query'
END
GO
CREATE PROCEDURE dummyquery
-- alter procedure dummyquery
AS
BEGIN
WITH prevCTE
AS
(
)
SELECT id
,idvalue
, idvalue
FROM Myid
END
GO
EXEC tsqlt.run 'WindowTests' ;
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 7, 2016 at 9:53 am
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply