Paging functionality on two tables combine with UNION

peter

4y1.1kSQL

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)

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

peter4y ago
That would only count the rows for the returned data.not the whole table which is required for paging!
Thanks again.
Priyanka K S4y ago
yes, you can add TotalRows=Count(id), you will get the total row count.
peter4y ago
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 S4y ago
1. declare @startRow int
2. declare @PageCount int
3. set @startRow = 0
4. set @PageCount = 20
6. select * from (
7. SELECT
8. Inbox_ProductID, Inbox_RefItemID, InboxID, inbox_messages.ID , MessageType, SenderID,
9. RecieverID, [Subject], Body, Datentime, Sender_Status, Reciever_Status
10. FROM Inbox_Messages
11. UNION
12. SELECT
13. ProductID, RefItemID,id, DISPUTE_INBOXID, MessageType, SenderID, RecieverID, [Subject], Body, Datentime, Sender_Status, Reciever_Status
14. FROM DISPUTE )
15. as tmpTable order by col1 offset @startRow rows fetch next @PageCount rows only
Hi,
try using offset and fetch.
thanks