rownumber function

Question

Can this function accept parameter in the order clause?

WITH LogEntries AS ( 
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description 
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @SORTCOLUMN". But I could not get this to work properly.

Answer 1

One way is to use Dynamic sql. See this

http://msdn.microsoft.com/en-us/library/ms188001.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 2

I agree that probably the best answer here is to use dynamic SQL.  Another less general solution that you might be able to take advantage of if the number of selections is small is to use CASE syntax in your order by syntax.  For example:

with logEntries as
( …
)
select
  Row,
  Date,
  Description
from logEntries
order by
  case @sortColumn
    when 'Row' then Row
    when 'Date' then Date
    when 'Description' then Description
  end

If you need to vary with both ascending and descending then additional modifications is needed.  Also note that you might need to cast row as varchar and front fill it to get it to work correctly.  Hang on and I will see if I can get something of a better example; my example is really a bit to crude.

EDIT:

On second thought, I am just going to abandon this; dynamic SQL seems like the better option anyway and unless you feel that the CASE is the way you want to go it seems best to just let this drop.

Answer 3

Example -

USE AdventureWorks2008R2
--SELECT * FROM Person.Person;

DECLARE @Orderby NVARCHAR(100) ='LastName' --'FirstName'
DECLARE @Str NVARCHAR(MAX);

SET @Str=';WITH CTE(BusinessEntityID,LastName,Rn) AS
         (
          SELECT BusinessEntityID,LastName
				,ROW_NUMBER() OVER (ORDER BY '+ @Orderby+') AS RN 
		  FROM AdventureWorks2008R2.Person.Person
		  )
		  SELECT * FROM CTE;' 

--PRINT @Str;

EXEC SP_EXECUTESQL @Str;

---

Narsimha

Answer 4

Hi Money.

There are several options:
 
1. Use dynamic SQL to generate the entire SELECT statement
2. Use CASE expression in the ORDER BY clause like:
 ORDER BY case @SortColumn when 1 then col1 end,
case @SortColumn when 2 then col2 end
3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.
 
There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

---

Iric Wen

TechNet Community Support

Answer 5

Or even

with logEntries as
( …
)
select
  Row,
  Date,
  Description,
  
case @sortColumn
    when 'Row' then Row
    when 'Date' then Date
    when 'Description' then Description end as sort_col

from logEntries
order by sort_col
  

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance