T-SQL returns the previous or next value

The "previous" concept implies a logical ordering, but we know that the rows in the table are in a non-bony order, and we need to provide a concept that is equivalent to the "previous" and can be expressed in T-SQL expressions. An example of this logical equivalence is the "maximum value less than the current value". In T-SQL, you can use a related subquery such as the following to express the phrase:

SELECT Orderid,orderdate,empid,custid,

(SELECT MAX (O2.orderid) from order as, WHERE 02.orderid<01.orderid) As Prevorderid

From order as 01;

Note that because there are no orders before the first one, the subquery returns a null value for the first order.

Similarly, the "next" concept can be expressed as "a minimum value greater than the current value." Below is a T-SQL query that returns the next order ID for each order.

SELECT Orderid,ordate,empid,custid

(SELECT MIN (OrderID) from order as, WHERE 02.orderid>01.orderid) As Nextorderid

From Orders as 01;

Note that because there will be no orders after the last one, the subquery returns null for the last order.

This article is from the "Growth Cornerstone" blog, be sure to keep this source http://jeffxi.blog.51cto.com/8540759/1641101

T-SQL returns the previous or next value