How to Get Filter record in ROW_NUMBER() function and Union?

Question

I have one Union Query for getting result from table. When only first query execute that time this working fine but if in second(union part) also return result that time it will not work. My query like

Select * from (
Select  ROW_NUMBER() OVER(ORDER BY EmpID DESC) as RowNo,
    Emp.EMPID, EMP.FirstName
    From Emp
Union
Select ROW_NUMBER() OVER(ORDER BY EMPID DESC) as RowNo,
    Emp.EMPID, EMP.FirstName
    From Emp Inner Join EMPDetail On Emp.EmpID = EMPDetail.EMPID 
    Where EMPDetail.IsActive=True
    ) as _EmpTable where RowNo between 1 and 20

Please help me for this. I want to add paging using Row number. is there any other solution for this?

Answer 1

You'll need another subquery around the union, before doing the row_number()ing:

Select * 
from 
(
    Select  ROW_NUMBER() OVER(ORDER BY EmpID DESC) as RowNo,
        EmpID, 
        FirstName
    FROM
    (
        SELECT 
            Emp.EMPID, 
            Emp.FirstName
        From Emp

        Union

        Select 
            Emp.EMPID, 
            EMP.FirstName
        From 
            Emp Inner Join EMPDetail On Emp.EmpID = EMPDetail.EMPID 
        Where 
            EMPDetail.IsActive=True
    )
) as _EmpTable 
where 
    RowNo between 1 and 20

Answer 2

Possible this be helpful for you -

SELECT * 
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY e.EmpID DESC) AS RowNo
    FROM (
        SELECT /*TOP 20*/
            e.EmpID,
            e.FirstName
        FROM dbo.Emp e
        ORDER BY e.EmpID DESC

        UNION

        SELECT /*TOP 20*/
            e.EMPID,
            e.FirstName
        FROM dbo.Emp e
        WHERE EXISTS(
            SELECT 1 
            FROM dbo.EMPDetail ed
            WHERE e.EmpID = ed.EmpID
                AND ed.IsActive = 'TRUE'
        ) 
        ORDER BY e.EmpID DESC
    ) t
) t
WHERE t.RowNo BETWEEN 1 AND 20