Create a View where one column is selected from the Previous Row

Question

Create a View where one column is selected from the Previous Row

aberndt

SSCrazy

Points: 2003
More actions
May 19, 2014 at 2:44 pm

#396780

Hi - I'd like to create a view where I can select one value from the previous row in the table. Can a query be written that does this? I am on SQL Server 2008. For simplicity sake, I would be selecting Info 1, Info 2, Info 3 and Info 4 from row 5 but I would like Info 5 to come from row 4, does that make sense? Please let me know if I can provide any more information. Thank you in advance for your help.

Viewing 15 posts - 1 through 15 (of 35 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

aberndt (5/19/2014)
Hi - I'd like to create a view where I can select one value from the previous row in the table. Can a query be written that does this? I am on SQL Server 2008. For simplicity sake, I would be selecting Info 1, Info 2, Info 3 and Info 4 from row 5 but I would like Info 5 to come from row 4, does that make sense? Please let me know if I can provide any more information. Thank you in advance for your help.

Hi. You definitely need to provide more information before any code could be attempted. Remember that in set theory a table has no order. That means there is no previous or next row unless you order the unordered set.

From your vague description it sounds like maybe you are looking for a recursive cte. Try googling that and see if it is what you are looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Megha P SSCommitted Points: 1976 More actions · Answer 2

aberndt (5/19/2014)
Hi - I'd like to create a view where I can select one value from the previous row in the table. Can a query be written that does this? I am on SQL Server 2008. For simplicity sake, I would be selecting Info 1, Info 2, Info 3 and Info 4 from row 5 but I would like Info 5 to come from row 4, does that make sense? Please let me know if I can provide any more information. Thank you in advance for your help.

Hi, see below if it works..for you..use self join in view

create table test

( ID INT IDENTITY(1,1),

Info1 VARCHAR(50),

Info2 VARCHAR(50),

Info3 VARCHAR(50),

Info4 VARCHAR(50),

Info5 VARCHAR(50)

)

INSERT test

SELECT '1-info1', '1-info2', '1-info3', '1-info4', '1-info5'

UNION ALL

SELECT '2-info1', '2-info2', '2-info3', '2-info4', '2-info5'

UNION ALL

SELECT '3-info1', '3-info2', '3-info3', '3-info4', '3-info5'

UNION ALL

SELECT '4-info1', '4-info2', '4-info3', '4-info4', '4-info5'

UNION ALL

SELECT '5-info1', '5-info2', '5-info3', '5-info4', '5-info5'

UNION ALL

SELECT '6-info1', '6-info2', '6-info3', '6-info4', '6-info5'

SELECT t1.ID,t1.Info1,t1.Info2,t1.info3,t1.Info4,t2.Info5

FROM test t1

LEFT JOIN test t2 ON t2.ID = t1.ID-1

aberndt SSCrazy Points: 2003 More actions · Answer 3

I'm not sure if that would pertain or not. What I am trying to do is pull information from a table (Operation) that contains multiple operations per Work Order. The information that I am looking for will always be from the operation directly preceeding the one that I select. I want all information from the Press Operator line and only the Run information from the Press line. In most cases, I can order the table by Row Id and that will give me the correct order to get info from the preceding operation. The only probelm that will occur if I use the RowId is that some older data will be inaccurate. The data will exist, it just won't be correct but this is for a report that is only using current data. I hope this makes sense. I apologize, I have a lot of experience finding and working with SQL data, just not creating tables, etc.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

aberndt (5/20/2014)
I'm not sure if that would pertain or not. What I am trying to do is pull information from a table (Operation) that contains multiple operations per Work Order. The information that I am looking for will always be from the operation directly preceeding the one that I select. I want all information from the Press Operator line and only the Run information from the Press line. In most cases, I can order the table by Row Id and that will give me the correct order to get info from the preceding operation. The only probelm that will occur if I use the RowId is that some older data will be inaccurate. The data will exist, it just won't be correct but this is for a report that is only using current data. I hope this makes sense. I apologize, I have a lot of experience finding and working with SQL data, just not creating tables, etc.

There's a link in Sean's signature to an article which describes how to set up sample data. From your description, this problem looks quite straightforward.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aberndt SSCrazy Points: 2003 More actions · Answer 5

I am attempting to follow the instructions for trying to compile some test data and I keep getting the error message 'Invalid Column Name'. I am obviously missing something. Below is a sample of what I am trying to run:

SET IDENTITY_INSERT #mytable ON

INSERT INTO #mytable

(ROWID, WORKORDER_BASE_ID, WORKORDER_LOT_ID, RESOURCE_ID, SEQUENCE_NO)

SELECT '539561','39620','E','WI PRESSGR-CM10','20' UNION ALL

SELECT '539562','39620','E','WIPRESSOP','25' UNION ALL

SELECT '539563','39620','E','WI SAND-2','26' UNION ALL

SELECT '539564','39620','E','WI OVEN-2','30' UNION ALL

SELECT '539565','39620','E','WI PART COOLOFF','35' UNION ALL

SELECT '539566','39620','E','WI SAND-2','40' UNION ALL

SELECT '539567','39620','E','WI SAW-2','48' UNION ALL

SELECT '539568','39620','E','WI INSPECT','49' UNION ALL

SELECT '539569','39620','E','WI STAMP-1','50' UNION ALL

SELECT '539570','39620','E','WI REWORK','60' UNION ALL

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

aberndt (5/20/2014)
I am attempting to follow the instructions for trying to compile some test data and I keep getting the error message 'Invalid Column Name'. I am obviously missing something. Below is a sample of what I am trying to run:
SET IDENTITY_INSERT #mytable ON
INSERT INTO #mytable
(ROWID, WORKORDER_BASE_ID, WORKORDER_LOT_ID, RESOURCE_ID, SEQUENCE_NO)
SELECT '539561','39620','E','WI PRESSGR-CM10','20' UNION ALL
SELECT '539562','39620','E','WIPRESSOP','25' UNION ALL
SELECT '539563','39620','E','WI SAND-2','26' UNION ALL
SELECT '539564','39620','E','WI OVEN-2','30' UNION ALL
SELECT '539565','39620','E','WI PART COOLOFF','35' UNION ALL
SELECT '539566','39620','E','WI SAND-2','40' UNION ALL
SELECT '539567','39620','E','WI SAW-2','48' UNION ALL
SELECT '539568','39620','E','WI INSPECT','49' UNION ALL
SELECT '539569','39620','E','WI STAMP-1','50' UNION ALL
SELECT '539570','39620','E','WI REWORK','60' UNION ALL

You are missing the create table statement. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

aberndt SSCrazy Points: 2003 More actions · Answer 7

I did that first. Even if I add that statement to the rest, I still receive the same error.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

aberndt (5/20/2014)
I did that first. Even if I add that statement to the rest, I still receive the same error.

Since I can't see you screen it would be very helpful if you would post what you tried. Then we can help you figure out what is wrong.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

aberndt SSCrazy Points: 2003 More actions · Answer 9

Here you go.

CREATE TABLE #mytable

(

ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

DateValue DATETIME,

Value DECIMAL(9,4),

YearValue INT,

MonthValue INT

)

SET IDENTITY_INSERT #mytable ON

INSERT INTO #mytable

(ROWID, WORKORDER_BASE_ID, WORKORDER_LOT_ID, RESOURCE_ID, SEQUENCE_NO)

SELECT '539561','39620','E','WI PRESSGR-CM10','20' UNION ALL

SELECT '539562','39620','E','WIPRESSOP','25' UNION ALL

SELECT '539563','39620','E','WI SAND-2','26' UNION ALL

SELECT '539564','39620','E','WI OVEN-2','30' UNION ALL

SELECT '539565','39620','E','WI PART COOLOFF','35' UNION ALL

SELECT '539566','39620','E','WI SAND-2','40' UNION ALL

And this is the error received: