Paging functionality on two tables combine with UNION

Question

I have two columns highlighted that are extra additions to make paging work. it is not correct i believe i need to unite tables first than combine the two extra parameters later to get the total rows returned and ROWID for paging functionality to operate proper.

SELECT TOP(@PageSize) FROM
(
SELECT RowID = ROW_NUMBER() OVER (ORDER BY datentime DESC),
Inbox_ProductID,
Inbox_RefItemID,
TotalRows=Count(Inboxid) OVER(),*
InboxID,
inbox_messages.ID ,
MessageType,
SenderID,
RecieverID,
[Subject],
Body,
Datentime,
Sender_Status,
Reciever_Status

FROM Inbox_Messages

UNION
SELECT RowID = ROW_NUMBER() OVER (ORDER BY datentime DESC),
ProductID,
RefItemID,
TotalRows=Count(id) OVER(),
id,
DISPUTE_INBOXID,
MessageType,
SenderID,
RecieverID,
[Subject],
Body,
Datentime,
Sender_Status,
Reciever_Status

FROM DISPUTE

)A WHERE A.RowID > ((@PageIndex-1)*@PageSize)

Priyanka K S · Answer

yes, you can add TotalRows=Count(id), you will get the total row count.

peter · Answer

That would only count the rows for the returned data.not the whole table which is required for paging! Thanks again.

peter · Answer

Thats an interesting proposal. Is it not possible to add the Totalrows return column. When i try to count rows and return value i am unable to return the total rows count as required for my paging function. Only current page row count is returned ie 10 when the total should be 24 Are you able to ammend the code to allow for paging? Thanks

Priyanka K S · Answer

declare @startRow int declare @PageCount int set @startRow = 0 set @PageCount = 20 select * from ( SELECT Inbox_ProductID, Inbox_RefItemID, InboxID, inbox_messages.ID , MessageType, SenderID, RecieverID, [Subject], Body, Datentime, Sender_Status, Reciever_Status FROM Inbox_Messages UNION SELECT ProductID, RefItemID,id, DISPUTE_INBOXID, MessageType, SenderID, RecieverID, [Subject], Body, Datentime, Sender_Status, Reciever_Status FROM DISPUTE ) as tmpTable order by col1 offset @startRow rows fetch next @PageCount rows only Hi, try using offset and fetch. thanks