I have a stored procedure below that returns a bunch of results. This stored procedure works exactly as I want it to. Except now I want to do paging within the stored procedure.
I have looked at various samples throughout the Net but don't quite get it. None of the examples I have seen have any form of SELECT statement with anything outside the ordinary. They also all seem to order by the IDENTITY column of the selected table or some OVER statement that allows the ORDER BY clause to use a column in the selected table. I want to order by a COUNT through a joined table.
It is entirely possible the count may be "0" for many of these items as well (no restaurants added as favourites yet and in the future obviously not all restaurants will be added as favourites). A variation excluding the 0 counted restaurants would be nice but most importantly I need a pageable version with or without the 0 counted restaurants.
I have basically no knowledge of paging so treat me as a complete novice in this. Also consider the passed variables as PageSize and PageNumber (1 based not 0 based) not StartRow and EndRow as I have seen in some examples.
SELECT Restaurant.* FROM Restaurant
LEFT JOIN (SELECT RestaurantId, COUNT(*) AS RecCount FROM UserFavourites GROUP BY RestaurantID ) As C
WHERE Restaurant.Active = 1
ORDER BY C.RecCount DESC