SQL Select Next Row and SQL Select Previous Row with Current Row using T-SQL CTE
SQL developers and SQL Server Administrators who code in t-sql may need to build sql SELECT statements where they select next row and select previous row with the current row selection.
For example, you sort the employee records of your company in your HR database by using an ORDER By statement according to their ID 's for instance.
Then you may want to select the previous and next records of a given id.
If in your company database table where employee information is kept, the employee id 's are in sequential order without any gap between employee id's then you can directly work on the Employee table without any problem.
All you have to do is build self joining queries as I did in the second part of my example t-sql select statement.
But if there is gaps between the Employee Id 's in your database, then you can not build your sql select statement so easy.
I used a CTE query where I also select the row number of a table row using the ROW_NUMBER() function.
Both sql CTE (Common Table Expression) queries and ROW_NUMBER() function are introduced to t-sql developers with Microsoft SQL Server 2005 database enhancements nd improvements.
At the end of this sql tutorial or sql tips article, I'll be adding links to the CTE and ROW_NUMBER tutorials and resources. You can use these resources as a sql reference article.
As you will see in the below SQL CTE example, the output of the CTE expression includes all employee data ordered by employeeid column values.
The ROW_NUMBER() function assures that there is no gap between each following database table records of the CTE select expression.
And the second part of the below t-sql script is only a SELECT with two more joins to the CTE table itself.
The joining criteria is important of course. We have to use the current row id and add 1 for the next row and substract 1 for the previous row in our example CTE select statement.
It is important to use LEFT JOIN 's otherwise the first and the last rows in the CTE ordered by EmployeeId column will not show up in resultset.
WITH CTE as (
RN = ROW_NUMBER() OVER (ORDER BY EmployeeID),
FROM CTE [Current Row]
LEFT JOIN CTE [Previous Row] ON
[Previous Row].RN = [Current Row].RN - 1
LEFT JOIN CTE [Next Row] ON
[Next Row].RN = [Current Row].RN + 1
[Current Row].EmployeeID = 5
And the output will be similar to shown in below screenshot.
Note that I changed the select columns for the sake of showing the previous, current and the next row explicitly.
CTE (Common Table Expressions) Links
SQL Server 2005 T-SQL Enhancements : CTE (Common Table Expressions)
Create a Numbers Table using CTE in MS SQL Server 2005 or SQL2008 Databases
Building SQL Calendar Week Based using CTE
How to Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function with CTE
ROW_NUMBER (Common Table Expressions) Links
SQL - SELECT TOP n or SELECT TOP Random n Rows From a Table For Each Category or Group
Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) to Delete Duplicate Rows in SQL Table
T-SQL Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)