SQL Server : With Syntax

Question

I have a table of employeeSomething

tb_EmployeeSomething

EmpID| TypeID
1 | 1
1 | 1
1 | 1
1 | 2
1 | 2
2 | 2
2 | 2
2 | 2

Now I'm using paging in my program and the return must be

EmpID| TypeID| RowNum
1|1|1
1|2|2
2|2|3

Here what I've tried

Declare @start int
Declare @end int

With Tmp2 as (
    With Tmp1 as (
        Select Distinct EmpID, TypeID 
        From tb_deductionBalance
    )
    Select *, row_number() OVER ( order by employeeID ) as RowNum 
    From Tmp1
)
Select * From Tmp2
Where RowNum Between @Start and @End

I'm not sure if there a better way to do this.

Answer 1

Use ROW_NUMBER() function to do it. You can find information about ROW_NUMBER here: http://technet.microsoft.com/en-us/library/ms189798.aspx

Answer 2

You don't nest Common Table Expressions (CTEs), you comma separate them:

;With Tmp1 as (
        Select Distinct EmpID, TypeID 
        From tb_deductionBalance
), Tmp2 as (
    Select *,
       row_number() OVER ( order by empID /* no employeeID in Tmp1 */) as RowNum 
    From Tmp1
)
Select * From Tmp2
Where RowNum Between @Start and @End

Any CTE may reference any earlier CTE.