CODEWITHSUNDEEP
sqlsql-server
Nikki
Nikki

Reputation: 441

SQL Server use union all and paging

How to apply paging when we are using union all to get result from two tables.below is the code here i am getting duplicate values in 'row_num' column which has been used for paging.

;WITH resultSetCTE AS 
  (
SELECT
    ROW_NUMBER() OVER 
    (
        ORDER BY nr.is_read,nr.is_read_cashier,
    CASE @columnSortNameDirection WHEN 'Pin' THEN r.name END ASC,
    CASE @columnSortNameDirection WHEN 'PinDesc' THEN r.firstname END DESC  
    ) AS 'row_num',
    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId'


    UNION ALL

    SELECT
    ROW_NUMBER() OVER 
    (
    ORDER BY nr.is_read,nr.is_read_cashier,
    CASE @columnSortNameDirection WHEN 'Pin' THEN r.name END ASC,
    CASE @columnSortNameDirection WHEN 'PinDesc' THEN r.firstname END DESC  
    ) AS 'row_num',
    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId'

 )
    SELECT *,
 (SELECT COUNT(1) FROM resultSetCTE) AS 'RecordCount' 
 FROM resultSetCTE 
 WHERE row_num BETWEEN (1 - 1) * 15 + 1 AND 1 * 15  
 ORDER by IsRead,IsReadCashier

Upvotes: 2

Views: 6913

Answers (2)

Juozas
Juozas

Reputation: 935

Please use offset / fetch instead. It solves all your problems. More info here: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

Code example (please update by your needs):

;WITH [resultSetCTE] AS 
 (
    SELECT
         [col] = [object_id] 
    FROM 
        [sys].[tables]

    UNION ALL

    SELECT
         [col] = [object_id] 
    FROM 
        [sys].[tables]
)
SELECT 
     * 
FROM 
    [resultSetCTE] 
ORDER BY 
     [col]
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

Upvotes: 3

Chandan Rai
Chandan Rai

Reputation: 10369

though you haven't posted the complete query, you could try something like this

declare @columnSortNameDirection varchar(5)
;WITH resultSetCTE AS 
  (

select ROW_NUMBER() OVER 
(
        ORDER BY nr.is_read,nr.is_read_cashier,
    CASE @columnSortNameDirection WHEN 'Pin' THEN r.name END ASC,
    CASE @columnSortNameDirection WHEN 'PinDesc' THEN r.firstname END DESC  
) AS 'row_num',*  from (    SELECT    
    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId'
    UNION ALL
    SELECT

    r.clave_receiver AS 'Pin',
    r.id_branch AS 'Id_Branch',
    r.id_receiver AS 'Id_receiver',  
    s.name_sender AS 'SenderFullName',
    r.name_receiver AS 'ReceiverFullName',
    r.id_flag_receiver AS  'Status',
    pas.option_name AS 'ApprovalStatus',
    r.mode_pay_receiver AS 'PaymentModeId') as A

 )
  Select *, 
 (SELECT COUNT(1) FROM resultSetCTE) AS 'RecordCount' 
 FROM resultSetCTE 
 WHERE row_num BETWEEN (1 - 1) * 15 + 1 AND 1 * 15  
 ORDER by IsRead,IsReadCashier

Upvotes: 2

Related Questions