USE market
GO
ALTER PROCEDURE offers_list
@CurrentPage int, @PageSize int, @TotalItems int output
AS
SET NOCOUNT ON

DECLARE @v_FirstOID bigint
DECLARE @v_LastOID bigint
DECLARE @v_FirstDate datetime
DECLARE @v_LastDate datetime
DECLARE @v_MaxRowNumber int

SET @v_MaxRowNumber = @CurrentPage * @PageSize 
SET @TotalItems = 0
SELECT @TotalItems = @TotalItems + 1 
,@v_FirstOID = CASE WHEN (@TotalItems = @v_MaxRowNumber - (@PageSize - 1)) THEN abt.Offer_id
ELSE @v_FirstOIDEND
,@v_FirstDate = CASE WHEN (@TotalItems = @v_MaxRowNumber - (@PageSize - 1)) THEN abt.theDate
ELSE @v_FirstDate END
,@v_LastOID = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.Offer_id
ELSE @v_LastOID END
,@v_LastDate = CASE WHEN @TotalItems <= @v_MaxRowNumber THEN abt.theDate
ELSE @v_LastDate END
FROM
(
  SELECT Offer_id, theDate  
  FROM Offers AS ofe  
  INNER JOIN TheUsers As us On ofe.User_num = us.User_id
  INNER JOIN Cities As city On ofe.city_num = city.City_id
) AS abt

  ORDER BY abt.theDate Desc

SELECT Offer_id, theDate, Title, us.Company_name, city.City_name    FROM Offers AS ofe    
INNER JOIN TheUsers As us On ofe.User_num = us.User_id
INNER JOIN Cities As city On ofe.city_num = city.City_id
Where (theDate <= @v_FirstDate) AND (theDate >= @v_LastDate)

ORDER BY theDate Desc
SET NOCOUNT OFF
GO

...
ORDER BY abt.Offer_id Desc

SELECT Offer_id, theDate, Title, us.Company_name, city.City_name    FROM Offers AS ofe
INNER JOIN TheUsers As us On ofe.User_num= us.User_id
INNER JOIN Cities As city On ofe.city_num = city.City_id        
WHERE (theDate <= @v_FirstDate) AND (theDate >= @v_LastDate)    

ORDER BY Offer_id Desc
...

...
ORDER BY abt.Offer_id Desc

SELECT Offer_id, theDate, Title, us.Company_name, city.City_name    FROM Offers AS ofe
INNER JOIN TheUsers As us On ofe.User_num= us.User_id
INNER JOIN Cities As city On ofe.city_num = city.City_id        
WHERE (theDate <= @v_FirstDate) AND (theDate >= @v_LastDate)    

ORDER BY theDate Desc
...

quote:

---

I have run some preliminary tests and found this.

potential resultset i.e. rowcount <1000
The solution I posted is twice as quick for the same resultset.

On resultset rowcount > 10000
The solution I posted was over 2.5 times quicker.

Another thing to note, I tried this out with one of our more complex queries which contains mutliple subqueries and a function call and I noticed this.
Rowcount is applied to the inner resultsets aswell, meaning it can potentially skew the results.
So it goes down to again, whatever suits you and is easiest to work with.

---

quote:

---

what you are trying to do appears to be very similiar (but unneedlessly more complicated and less efficient) then a technique I discuss here:

---