Uber Complex Query for Paging

[Signit]

I am trying to create a Query which will allow me to do paging. The Query below is intended to select the name of a recipe, it's ID, an average of all associated ratings, a total of all associated ingredients, and a count of specified ingredients. The query effectively does all of that. However, what it is not doing is allowing me to page correctly. I always get the same data at the top. I have used a similar page query in other places and it has worked effectively however, it is not reversing the way it typically does. Any advice would be much appreciated.

SELECT TOP 3 * FROM (SELECT TOP 3 * FROM (SELECT * FROM (SELECT R.recipeName, (SELECT Avg(ratingValue) FROM ratingList L WHERE L.recipeID = R.recipeID) AS avgRatingValue, (SELECT COUNT(recipeIngredients.ingredientID) FROM recipeIngredients WHERE R.recipeID = recipeIngredients.RecipeID ) AS totIngredientCount, R.recipeID, COUNT(I.ingredientID) AS myIngredientCount FROM recipes AS R INNER JOIN recipeIngredients AS I ON R.recipeID=I.RecipeID WHERE (I.ingredientID='ing015' OR I.ingredientID='ing011' ) GROUP BY R.recipeID, R.recipeName ORDER BY R.recipeName) as foo ORDER BY (totingredientcount-myingredientcount), recipename) as bottom ORDER BY (totingredientcount-myingredientcount), recipename DESC) as flip ORDER BY (totingredientcount-myingredientcount), recipename

 

[r937]

you forgot a DESC

...
GROUP
BY R.recipeID, R.recipeName
) as foo
ORDER
BY ( totingredientcount
- myingredientcount )
, recipename
) as bottom
ORDER
BY ( totingredientcount
- myingredientcount ) DESC
, recipename DESC
) as flip
ORDER
BY ( totingredientcount
- myingredientcount )
, recipename

rudy

http://r937.com/

SQL Consulting