This tutorial will show how to create a query that returns previous and next values of a table column in SQL Server 2008 R2. Since rows in a table have no order, we need to come up with a T-SQL expression that returns these values. One such expression is “the maximum value that is smaller than the current.” We will use this logical expression to create two correlated subqueries that return previous and next values in a table.

Setting Up

In these examples, we will use a database called Products with a table called Product. To create a database in SQL Server, we use the CREATE DATABASE statement followed by a desired database name, in this case it will be Products.

From this point on, every query will have the USE statement along with the Products database. Let’s create the Product table in the Products database.The Product table will have four columns: Prod_Id, ProductName, Unit, and UnitPrice. The Prod_Id will be of the int data type, ProductName and Unit will be of the nvarchar data type, and the UnitPrice will be of the float data type.

To create a table in SQL Server, we use the CREATE TABLE statement followed by a table name and desired columns wrapped in parenthesis.

Now that we have a table, let’s insert values into it so we have data to write queries for. To insert multiple values in a single query, we use three statements: INSERT INTO, SELECT, and UNION. The INSERT INTO statement specifies which table and columns to insert the values into. The SELECT statement selects the actual values being inserted and the UNION statement prevents duplicates from being inserted.

Previous Values Correlated Subquery

This example will show how to create a correlated subquery that returns the previous values in the table. In a subquery there are two queries, an inner and outer. Each are assigned a table alias that refer to the same table. First we will use the SELECT statement with an asterisk(*) to select all columns from the table for the outer query. Next we will wrap the inner query in parenthesis and use the SELECT statement along with the MAX function to select the highest product ID from the Product table. We then use a WHERE statement to filter out product IDs in the inner query that are less than the product IDs in the outer query.

Output

Execute the query and there will be an extra column, PreviousProductId, that returns the previous product IDs. Notice how items with a product ID of 1 have a PreviousProductID of null. This is because there are no product IDs with a value less than 1.

Next Values Correlated Subquery

This subquery will be exactly the same as the previous example except for three differences: the MAX function is replaced with the MIN function, the ‘less than’ symbol is replaced with a ‘greater than’ symbol, and the column alias is changed to “NextProductID.”

Output

Execute the query and the NextProductId column returns the next product ID in the table.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Download Source Files