T-SQL dynamic ordering for a common pagination table

Question

I found some great SQL code for dynamic sorting and ordering, but I'm wondering if anyone can help me flip it so that CTE in PROC uses dynamic sort / order below. This code works, but the result is not what I get after ORDER BY p.ProductId happens first in CTE, then the ORDER BY CASE statement applies only to records 6-10

DECLARE @Skip int DECLARE @Take int DECLARE @OrderBy VARCHAR(50) SET @Skip = 5; SET @Take = 5; SET @OrderBy = 'PriceAsc'; WITH ProductCT AS ( SELECT ROW_NUMBER() OVER(ORDER BY p.ProductId) AS RowNum , p.ProductId FROM dbo.Product AS p ) SELECT p.ProductId ,p.Title ,p.Price FROM dbo.Product AS p INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId WHERE pct.RowNum BETWEEN @Skip + 1 AND (@Skip + @Take) ORDER BY CASE WHEN @OrderBy = 'TitleAsc' THEN (RANK() OVER (ORDER BY p.Title)) WHEN @OrderBy = 'TitleDesc' THEN (RANK() OVER (ORDER BY p.Title DESC)) WHEN @OrderBy = 'PriceAsc' THEN (RANK() OVER (ORDER BY p.Price)) WHEN @OrderBy = 'PriceDesc' THEN (RANK() OVER (ORDER BY p.Price DESC)) ELSE (RANK() OVER (ORDER BY p.Price)) END 

Thanks in advance for any suggestions :-)

Answer 1

I initially marked @Johan's answer as correct because it worked, but I was a little unsure of the INNER JOIN and the overall complexity of this request.

I talked with my colleague about the problem and he came up with this very neat solution (thanks Tom !!), so I decided to share with him:

 DECLARE @Skip int DECLARE @Take int DECLARE @OrderBy VARCHAR(50) SET @Skip = 5; SET @Take = 5; SET @OrderBy = 'PriceAsc'; WITH ProductCT AS ( SELECT CASE WHEN @OrderBy = 'TitleAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Title) WHEN @OrderBy = 'TitleDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Title DESC) WHEN @OrderBy = 'PriceAsc' THEN ROW_NUMBER() OVER (ORDER BY p.Price) WHEN @OrderBy = 'PriceDesc' THEN ROW_NUMBER() OVER (ORDER BY p.Price DESC) END AS RowNum , p.ProductId FROM dbo.Product AS p ) SELECT p.ProductId ,p.Title ,p.Price FROM dbo.Product AS p INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId WHERE pct.RowNum BETWEEN @Skip + 1 AND (@Skip + @Take) ORDER BY RowNum 

Answer 2

Here is a solution that should work.

 WITH ProductCT AS ( SELECT ROW_NUMBER() OVER(ORDER BY p.Title) AS RowNum1 ,ROW_NUMBER() OVER(ORDER BY p.Title DESC) AS RowNum2 ,ROW_NUMBER() OVER(ORDER BY p.Price) AS RowNum3 ,ROW_NUMBER() OVER(ORDER BY p.Price DESC) AS RowNum4 ,p.ProductId FROM dbo.Product AS p ) SELECT p.ProductId ,p.Title ,p.Price FROM dbo.Product AS p INNER JOIN ProductCT AS pct ON pct.ProductId = p.ProductId AND ((@OrderBy = 'TitleAsc' AND pct.RowNum1 BETWEEN @Skip + 1 AND (@Skip + @Take)) OR (@OrderBy = 'TitleDesc' AND pct.RowNum2 BETWEEN @Skip + 1 AND (@Skip + @Take)) OR (@OrderBy = 'PriceAsc' AND pct.RowNum3 BETWEEN @Skip + 1 AND (@Skip + @Take)) OR (@OrderBy = 'PriceDesc' AND pct.RowNum4 BETWEEN @Skip + 1 AND (@Skip + @Take)) ) ORDER BY CASE @OrderBy WHEN 'TitleAsc' THEN RowNum1 WHEN 'TitleDesc' THEN RowNum2 WHEN 'PriceAsc' THEN RowNum3 WHEN 'PriceDesc' THEN RowNum4 ELSE RowNum3 END 

I think JOIN might be better written, but now I have no more time.

Answer 3

Rob solutions for some reason do not work for me, here is my modified version that worked perfectly.

  SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortOrder = 'TitleAsc' THEN p.Title WHEN @sortOrder = 'TitleDesc' THEN p.Title WHEN @sortOrder = 'PatientId' THEN p.Title WHEN @sortOrder = 'PriceAsc' THEN p.Title WHEN @sortOrder = 'PriceDesc' THEN p.Title END ASC) AS [RowNum], ................. ..................