Dynamic Order By in ROW_NUMBER()

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s