• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

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
0
rt2001
Asked:
rt2001
  • 5
  • 2
1 Solution
 
Reza RadCommented:
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 RadCommented:
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
 
rt2001Author 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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now