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

Posted on 2009-12-27
Last Modified: 2012-05-08

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:


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


P.S Please post a solution, not just URLs
Question by:rt2001
    LVL 30

    Expert Comment

    by:Reza Rad
    could you bring some sample of data, and sample requested results?

    Author Comment

    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.

          Right(Min(Convert(varchar(16),T1.ABC,120)+T1.DEF),3) AA,
          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


    Author Comment

    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,

    Author Comment


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

    Expert Comment

    by:Reza Rad
    seems you solve it yourself,
    Glad to hear it

    LVL 1

    Expert Comment

    how did you resolve I would like to know thanks

    Author Comment

    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.

    Accepted Solution

    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

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now