How to do order by in your paging using ROW_NUMBER() feature on SQL Server 2005
SELECT a.CurrentPrice, a.LotQuantity, a.IsAuction, a.AuctionID, a.AuctionName, a.DateStart, a.DateFinish, a.StartingPrice, a.ReservePrice, a.FixedPrice, a.BidIncrement, p.ProductID, p.CategoryID, p.BrandID, p.ProductName, p.ContentID, p.ThumbImageID, Row_Number() OVER ( ORDER BY CASE WHEN @OrderBy = 'Price_ASC' THEN CurrentPrice WHEN @OrderBy = 'ClosingSoon_ASC' THEN ISNULL(DateFinish, DATEADD(DAY, 100, GETDATE())) END ASC, CASE WHEN @OrderBy = 'Price_DESC' THEN CurrentPrice WHEN @OrderBy = 'ClosingSoon_DESC' THEN ISNULL(DateFinish, DATEADD(DAY, 100, GETDATE())) END DESC, CASE WHEN @OrderBy = 'Name_ASC' THEN AuctionName END ASC, CASE WHEN @OrderBy = 'Name_DESC' THEN AuctionName END DESC ) AS RowNum FROM Auction a INNER JOIN Products p ON a.ProductID = p.ProductID INNER JOIN [Content] c ON p.ContentID = c.ContentID
Leave a Reply