OVER clause. How to order by multiple columns with

问题:

The following SQL code is a sample of larger SQL statement. My question is how can I do CASE multiple column ORDER BY in the SELECT ROW_NUMBER(), similar to the one below comment line. The presented code works, but I need to order by two columns.

I am using MSSQL 2008

SELECT TOP(50)
    ROW_NUMBER() OVER(ORDER BY CASE WHEN @OrderBy = 'Total' THEN SUM(TotalViews) ELSE SUM(LastMonthViews) END DESC) AS Position
    ,SUM(Albums.TotalViews) AS TotalViews
    ,SUM(Albums.LastMonthViews) AS LastMonthViews
FROM Albums

--The code to be implemented in the SELECT ROW_NUMBER()
ORDER BY SUM(LastMonthViews) DESC, SUM(TotalViews) DESC

回答1:

You can repeat the case statement in the row_number() partitioning clause:

SELECT TOP(50)
        ROW_NUMBER() OVER (ORDER BY (CASE WHEN @OrderBy = 'Total' THEN SUM(LastMonthViews) ELSE SUM(LastMonthViews) END) DESC,
                                    (CASE WHEN @OrderBy = 'Total' THEN SUM(TotalViews) ELSE SUM(LastMonthViews) END) DESC
                          ) AS Position
    ,SUM(Albums.TotalViews) AS TotalViews
    ,SUM(Albums.LastMonthViews) AS LastMonthViews
FROM Albums;