Calculating second page of results from a union

Question

I've got a SQLServer problem.

I'm trying to work out how to show a second page of results. The first page of results are pulled using the query below.

SELECT TOP 10 * FROM (
    SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
    FROM tblclassifieds c
    LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
    LEFT JOIN ftblstates ON c.stateid = ftblstates.id
    WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
    UNION ALL
    SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
    FROM tblclassifiedemployers ce
    LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
    WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
) a
ORDER BY inserteddate desc;

Is the answer similar to the following?

SELECT TOP 10 * FROM (
    SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
    FROM tblclassifieds c
    LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
    LEFT JOIN ftblstates ON c.stateid = ftblstates.id
    WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
    UNION ALL
    SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
    FROM tblclassifiedemployers ce
    LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
    WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
) a

WHERE NOT a.id IN (SELECT TOP 10 tblclassifieds.id ...

ORDER BY inserteddate desc;

Can someone please help me out completing the query?

Looks like maybe you are trying to work out the general pattern for paging SQL queries. If so, your question is probably a duplicate of "http://stackoverflow.com/questions/1418105/paging-design-recommendation-for-asp-net-and-sqlserver-2005/1418258#1418258 — Joel Lee, May 17 '11 at 05:31
http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server — mu is too short, May 17 '11 at 05:37
Question referenced by @mu is more specific to your question than the one I mentioned above. But note that answers to both questions require use of the `row_number()` function, which is available in SQL Server >= 2005. For older versions, your only tool is to select `TOP N` as in your example. Assuming you want the fourth page, 10 rows per page, you need to (1) SELECT all rows, sorted in some desired order. (2) Add lines to SELECT top 4*10 rows, sorted in reverse order, (3) Add more lines to query to select top 10 rows from those 40, this time sorted in desired order. — Joel Lee, May 17 '11 at 06:07
@Joel: So I guess the question becomes "which version of SQL Server does Ben have?" — mu is too short, May 17 '11 at 06:42

Johan · Answer 1 · 2011-05-24T09:22:23.623

A simple way of doing it:

declare @page int = 2
declare @take int = 10

select * from (
    SELECT *, ROW_NUMBER() OVER(ORDER BY inserteddate desc) as rowno FROM (
        SELECT c.id, c.name, c.inserteddate, c.cityname, ftblstates.name AS statename, clc.name AS catname, '' AS listingimagelogo, '' AS orgname, relocateyn, '' AS employerclassified
        FROM tblclassifieds c
        LEFT JOIN tblclassifiedscategories clc ON c.categoryid = clc.id
        LEFT JOIN ftblstates ON c.stateid = ftblstates.id
        WHERE c.expirydate != '' AND c.expirydate >= '5/17/2011'
        UNION ALL
        SELECT ce.id, ce.name, ce.inserteddate, suburb AS cityname, ftblstates.name AS statename, ce.jobtype AS catname, ce.listingimagelogo, ce.orgname, '' AS relocateyn, '1' AS employerclassified
        FROM tblclassifiedemployers ce
        LEFT JOIN ftblstates ON ce.stateid = ftblstates.id
        WHERE ce.expirydate != '' AND ce.expirydate >= '5/17/2011'
    ) a
) b
where   rowno > (@page - 1) * @take 
    AND rowno <= @page * @take
order by rowno

I didnt alter your internal query.

Edit: Added parenteses in where clause

Many thanks Johan for your help! You saved me alot of time :) — Ben, May 24 '11 at 01:22
I just had to make a few changes to get the pages to display the correct amount of results. WHERE rowno > @page * @take - @take AND rowno <= @page * @take ORDER BY rowno — Ben, May 24 '11 at 01:57
Ok, I see now that I should have put parenteses oround @page - 1 to make it work, but your solution works just fine. — Johan, May 24 '11 at 09:21

Calculating second page of results from a union

1 Answers1