So asuming we have a tables called TreeEmployees we will use ROW_NUMBER() sql function as an order index to the where clause.
Let's say we have a simle query
SELECT [Id]but we want to paginate this
,[ParentId]
,[Name]
,[Description]
,[Position]
FROM [Sample].[dbo].[TreeEmployees]
SELECT * FROM (This query will just take the first 10 records. Notice that first row will just have index 1 not 0.
SELECT [Id]
,[ParentId]
,[Name]
,[Description]
,[Position]
, ROW_NUMBER() OVER (ORDER BY Name) as row FROM TreeEmployees ) tree
WHERE row >= 1 and row <= 10
Id | ParentId | Name | Description | Position | row |
7 | 4 | Anna Mendez | Member of Joyce's team | Worker | 1 |
9 | 2 | Carter Bowls | Jan's team 1 | Team Leader | 2 |
2 | NULL | Jan Right | Jan folowers | Chief Account manager | 3 |
10 | 2 | Jimmy Clarck | Jan's team 2 | Team Leader | 4 |
1 | NULL | John Doe | John folowers | Technical Manager | 5 |
4 | 1 | Joyce May Ann | Joyce team | Team Leader | 6 |
6 | 3 | Laurel Bean | Member of Flynn's team | Worker | 7 |
13 | 10 | Lilly Ann Parker | Jimmy's team | Worker | 8 |
12 | 9 | Louise Senna | Carter's team | Worker | 9 |
8 | 4 | Maria Fernandez | Member of Joyce's team | Worker | 10 |