Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rsegecin
Yak Posting Veteran
82 Posts |
 Posted - 2007-07-05 : 02:31:13
|
| I was thinking in give the order straight through a string like:Select t.*, u.userType_id, u.nickname, Row_Number() over (order by 'u.nickname') ...I don’t think that is possible, but may be there is another efficient way. Would be very good do it in a fast and elegant way, because I need to request this same selection twice in the same procedure. Currently I’m creating a temp table, but I didn’t test with high amount of data, which I’m afraid to be too slow. with threads as (Select t.*, u.userType_id, u.nickname, Row_Number() over (order by case when @PageOrder = 0 and @PageWay = 1 then u.nickname end asc, case when @PageOrder = 1 and @PageWay = 1 then t.creation end asc, case when @PageOrder = 2 and @PageWay = 1 then t.posts end asc, case when @PageOrder = 3 and @PageWay = 1 then t.title end asc, case when @PageOrder = 4 and @PageWay = 1 then t.views end asc, case when @PageOrder = 0 and @PageWay = 0 then u.nickname end desc, case when @PageOrder = 1 and @PageWay = 0 then t.creation end desc, case when @PageOrder = 2 and @PageWay = 0 then t.posts end desc, case when @PageOrder = 3 and @PageWay = 0 then t.title end desc, case when @PageOrder = 4 and @PageWay = 0 then t.views end desc ) as RowNumber from solarThreads as t inner join solarUsers as u on t.user_id = u.user_id where (t.forum_id = @forum_id and t.father = 1 and t.type = @PageType and t.creation > @Period))Select * from threads where RowNumber between @FirstRow and @LastRowThank you very much. |
|
|
|
|
|
|
|
There is any way to simplify this?