ROW_NUMBER() function in SQL 2005

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.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

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.

Thanks,

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.

|||Thank you so much

Rounding to the nearest thousand

Hi

Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?

Thanks,
Neil

Hello Neil,

Try this:

=Round(Fields!Field1.Value, 3)

Hope this helps.

Jarret

|||

Sorry, I mis-read your question. I thought it said thousandth.

This should do what you want:

=cInt(Fields!Field1.Value / 1000) * 1000

Jarret

|||

For formatting it is

#,###,.

|||Thanks Jarret, Just what I needed..

Thanks!!
|||Thanks Ewild!

You read my mind!! Also just what I needed