How to work a Count clause into this query?

Question

I have a rather expensive query that returns a page of results:

SELECT * FROM 
 (SELECT   
  ROW_NUMBER() OVER (ORDER BY j.PostDate DESC) as Row,           
  FROM 
   JobListing j, 
   Location l, 
   City c,
   JobListing_Skill_XREF js,
   @SkillTable st     
  WHERE 
   DistanceBetween(@lat,@long, c.Lat,c.Long) <= @miles AND
   js.Skill_ID = st.id AND      
   j.location = l.id AND
   j.id = js.JobListing_Id AND   
   l.CityID = c.Id    
 ) AS RESULTS
 WHERE Row Between (@PageNumber - 1) * @PageSize + 1 and (@PageNumber * @PageSize)

What I would like to do is also return the total count of the inner query so that I can calculate total pages.

However, I can't figure out how to thread a COUNT clause into it, and I really don't want to have to select this into a temp table or run it twice just to calculate page count.

Any Ideas?

Answer 1

Use a CTE? Something like (can't test obviously :) ...

WITH R (Row) 
AS 
(
  SELECT                                 
  ROW_NUMBER() OVER (ORDER BY j.PostDate DESC) as Row,
  FROM JobListing j, Location l, City c, JobListing_Skill_XREF js,@SkillTable st
  WHERE DistanceBetween(@lat,@long, c.Lat,c.Long) <= @miles AND 
    js.Skill_ID = st.id AND
    j.location = l.id AND
    j.id = js.JobListing_Id AND
    l.CityID = c.Id      
)
SELECT R.*, COUNT(R.*) AS [Count] FROM R
WHERE R.Row Between (@PageNumber - 1) * 
  @PageSize + 1 and (@PageNumber * @PageSize)

Answer 2

This is the best I was able to do, I'd love to know if people have better suggestions:

DECLARE @JobTable TABLE
(
 ...snip...
);
INSERT INTO @JobTable 
 SELECT   
  ROW_NUMBER() OVER (ORDER BY j.PostDate DESC) as Row,
  j.*      
  FROM 
   JobListing j, 
   Location l, 
   City c,
   JobListing_Skill_XREF js,
   @SkillTable st     
  WHERE 
   DistanceBetween(@lat,@long, c.Lat,c.Long) <= @miles AND
   js.Skill_ID = st.id AND      
   j.location = l.id AND
   j.id = js.JobListing_Id AND   
   l.CityID = c.Id 

SELECT * 
         FROM @JobTable
         WHERE 
               Row BETWEEN 
                 (@PageNumber - 1) * @PageSize + 1 
                 AND (@PageNumber * @PageSize)


SELECT @TotalRows = Count(1) FROM @JobTable;

Answer 3

SELECT * FROM 
        (SELECT                 
                ROW_NUMBER() OVER (ORDER BY j.PostDate DESC) as Row,
                COUNT(*) OVER() AS total
                FROM 
                        JobListing j, 
                        Location l, 
                        City c,
                        JobListing_Skill_XREF js,
                        @SkillTable st                    
                WHERE 
                        DistanceBetween(@lat,@long, c.Lat,c.Long) <= @miles AND
                        js.Skill_ID = st.id AND                                         
                        j.location = l.id AND
                        j.id = js.JobListing_Id AND                     
                        l.CityID = c.Id                         
        ) AS RESULTS
        WHERE Row Between (@PageNumber - 1) * @PageSize + 1 and (@PageNumber * @PageSize)