15,965,934 members
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

```Input
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```

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

What I have tried:

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

Solution 1

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.

Solution 2

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.

v3

Solution 3

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 ...

Top Experts
Last 24hrsThis month
 merano99 25 OriginalGriff 10 Alan N 10 Steve Mol 2022 10 Maciej Los 5
 OriginalGriff 318 Pete O'Hanlon 170 Dave Kreskowiak 145 merano99 115 Andre Oosthuizen 55

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