Click here to Skip to main content
15,936,337 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
1. what is the INITIAL BEFORE VALUE - it should be found in the after values or earlier rows.
2. what is the LATEST AFTER VALUE - it should be found in the before value of subsequent rows
3. In this example there 4 input sets are separate and put in sequence but in actual scenario the rows can be populated in random

Record 	Before Value	After value
1	101	102
2	102	105
3	105	108
4	108	289
5	289	109
6	109	109
7	109	110
8	110	110
9	201	201
10	201	202
11	203	301
12	301	150
13	150	150
14	86	86
15	86	123
16	123	97
17	97	97
18	97	784
19	269	269
20	38	38

Initial Before Value	Latest After Value
101	110
201	150
86	784
269	269
38	38

What I have tried:

Initial Before Value	Latest After Value
101	110
201	150
86	784
269	269
38	38
Updated 29-Apr-24 22:49pm

You are missing so much context here that it is impossible for us to come up with a reasoned view of what you are trying to solve. In your example, I have to assume that the space between sections is indicating that these are the groups you need to find the first value and last value from but, there's no indication how this would work in a database context.

To be honest, this sounds like a homework question and you have missed out parts of what has been asked. As we can't see your screen, we have no idea what you need to do to complete this. Also, you haven't shown what you have tried - you say this is a SQL question but you haven't actually put any SQL in there.
Share this answer
As Pete says, it is difficult to know what you are asking but here's my best guess.

You know that the "Before Value" of a row is taken from the "After Value" of the preceding row.

Row[N].Before_Value = Row[N-1].After_Value

A "group" of rows or an "input set" is terminated when this "rule" is not respected.

You can therefore traverse the rows of the table testing for this condition and when it is not true then you have found the end of one input set (the LATEST AFTER VALUE of the current input set) and the start of the next one (the INITIAL BEFORE VALUE of the next input set).

Doing that would give you the output that you have put into the question and the "What I have tried" section.

As an aside, there are 5 input sets and not 4 as you state in your question, this is shown in the output that you have provided.

As for what code would do that for you then you need to show what code you have tried (so people can help you improve it / fix it) or as an absolute minimum the language that you are supposed to be using.
Share this answer
To add to what the others have said, you might want to look at LEAD and LAG - SQL Server | Microsoft Learn[^]

Unless this is homework, in which case you only want to use them if the tutor has covered them yet ...
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900