CODEWITHSUNDEEP
sqlsql-serversql-server-2008sortingpagination
bcg
bcg

Reputation: 261

SQL Server select queries union all and limit

I have a lot of problems to migrate queries from Mysql to SQL Server. I have this query that is a bit complicated but in mysql it works fine.

I have a select from a table union all to a select from another table not equals and a limit to paginate the results because the rows returned are a lot. When I tried to migrate to SQL Server, the selects with the union work fine. When I read how to implement a limit in T-SQL, in stackoverflow and another pages I learned how to do but when I try to apply it to my query it does not run and SQL Server returns an error.

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

Can somebody please help me?

Upvotes: 0

Views: 1166

Answers (2)

Cato
Cato

Reputation: 3701

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

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

You have 2 separate rownumbers in here. Try this:

WITH Results_CTE AS
(
'false' as historico,'' as tabla,a.nombre,a.apellido1,a.apellido2 from persons a where a.eliminado = 'N' and ( idconv = 30 ) 
union all 
'true' as historico,b.tabla,b.nombre,b.apellido1,b.apellido2 from persons_hist b 
where b.eliminado = 'N' and ( tabla = '1997' )
) 

select *
from
(

select CTE1.*, row_number() over (order by apellido1 ) as RowNum
from Results_CTE
)    
where RowNum <=10

Upvotes: 1

Related Questions