SQL select to retrieve values that appear in the very next row

Hello,

Is there an SQL select expression that I can use to retrieve values that appear in the very next row of the same column?

Ideally some addition or modification of the following statement.

      Right(Min(Convert(varchar(16),TD.fCOff,120)+TD.DBase),3) AA

Maybe something like:

(Right(Min(Convert(varchar(16),TD.fCOff,120)+TD.DBase),3)).NEXT

..if possible.

This is because this statement pulls up values from the row just above the one I want (the reference values can at best lead to only these, but the right ones lurk in the line following these).

Thanks.

P.S Please post a solution, not just URLs
rt2001Asked:
Who is Participating?
 
rt2001Connect With a Mentor Author Commented:
Simple pseudo code of the solution:

-create temp table
-perform insert and select
-use over keyword to maintain numbered rows
-join table on to itself with variables T1 and T2 on T1.rownum + 1 = T2.rownum
0
 
Reza RadConsultant, TrainerCommented:
could you bring some sample of data, and sample requested results?
0
 
rt2001Author Commented:
Hi, Thank you for your reply.

Here is a sample.

In the excel sheet, times are shown for when we

(1)leave for a location from a start location (say A) and
(2)get on a flight to the destination location (B)

Up in the code I need to use a query to retrieve times for when we eventually leave the destination location B. And the times for 'leave here' and 'get on flt out of  here' are obtained from 'start out to get here' and 'get on flt for here' from the next row. There is no column in the dbase that contains these values for me to retrieve right off so I have to manipulate the table using SQL to retrieve these values. The sample shown constitutes one set in the table. All sets are logically identical to this with variation in the number of rows. Reason I need to do this in one line is because the actual query is kind of large already with 2 levels of nesting.


As shown in the query skeleton shown below, the line intended to do this needs to appear somewhere like that. So I don't know if the select required can have it's own where clause or what.


Select
      Right(Min(Convert(varchar(16),T1.ABC,120)+T1.DEF),3) AA,
      
      T2.LMN,
      Min(T1.HIJ) BB,

****appear somewhere here*****
      
      from T1
      inner join T2 on T1.OPQ = T2.OPQ
      Inner Join (
                  Select a,b,c,d
                  Group By a,b
                  ) SubQry On SubQryLMN=T1.LMN
      Group By T2.LMN, T1.HIJ


sample2.csv
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rt2001Author Commented:
Not sure but I tried this line...I am checking the results to see if it worked.......

(Select leave_here from Tablename where TI = current_value and DI = its_curr_val + 1) R,
0
 
rt2001Author Commented:
Hello,

This problem might be simpler than it looked at first...I may have managed to solve it...please hold off posting responses...thank you much for responses recd so far.
0
 
Reza RadConsultant, TrainerCommented:
seems you solve it yourself,
Glad to hear it
regards,

0
 
sqlman08Commented:
how did you resolve I would like to know thanks
0
 
rt2001Author Commented:
The resolution was to use a temporary table and then perform a self join on it such that row1 joined with row2. I will post the pseudo code for it shortly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.