bcg bcg - 1 month ago 5
SQL Question

SQL server select queries union all and limit

i have a lot of problems to migrate queries from Mysql to Sql server, i have the next query that it's a bit complicated but in mysql works fine. I have a select from a table union all to a select from another table not equals and a limit to paginate de results because the rows returned are a lot. When i tried to migrate to sql server the selects whith the union works fine. When i read how to implement a limit in sql in stackoverflow and another pages i learned how to do but when i try to applied it to my query not run and sql server returns me fail results. the query without limit is the following (this query works well):

select 'false' as historico,'' as tabla,a.nombre,a.apellido1,a.apellido2 from persons a where a.eliminado = 'N' and ( idconv = 30 )
union all
select 'true' as historico,b.tabla,b.nombre,b.apellido1,b.apellido2 from persons_hist b
where b.eliminado = 'N' and ( tabla = '1955' ) order by apellido1 asc


but when i try to add "pagination" for example 10 rows starting in 0 this query returns me 18 rows 9 rows from the first table and 9 rows from the second table, but i have to paginate for example 10 rows from the union of 2 tables.

;WITH Results_CTE AS
(

select ROW_NUMBER() OVER (ORDER BY apellido1 asc ) AS RowNum , 'false' as historico,'' as tabla,a.nombre,a.apellido1,a.apellido2 from persons a where a.eliminado = 'N' and ( idconv = 30 )
union all
select ROW_NUMBER() OVER (ORDER BY apellido1 asc ) AS RowNum , 'true' as historico,b.tabla,b.nombre,b.apellido1,b.apellido2 from persons_hist b
where b.eliminado = 'N' and ( tabla = '1997' )
)
SELECT *
FROM Results_CTE
WHERE RowNum >= 0
AND RowNum < 0 + 10


somebody can help me please?

Answer

one problem is that SQL SERVER row_number starts at 1 - you do not get a 0, so you have asked for 0 - 9 which will be 9 rows/

your other problem seems to be that row_number is calculated separately for each part of the UNION (which is logical for it to do so) - try calculating row number in a 2nd CTE

try

    ;WITH Results_CTE1 AS
(

    select  'false' as historico,'' as tabla,a.nombre,a.apellido1,a.apellido2 from persons a where a.eliminado = 'N' and ( idconv = 30 ) 
    union all 
    select  'true' as historico,b.tabla,b.nombre,b.apellido1,b.apellido2 from persons_hist b 
    where b.eliminado = 'N' and ( tabla = '1997' )
),
Results_CTE AS 
(
    SELECT *,ROW_NUMBER() OVER (ORDER BY apellido1 asc ) AS RowNum FROM Results_CTE1
)

SELECT *
FROM Results_CTE
WHERE RowNum BETWEEN 1 AND 10
Comments