August 26, 2009 at 2:49 am
You can use multiple CTE's within the same statement, heres a simple example.
with cteObjects(Name)
as
(
select Name from sysobjects where name like 'a%'
),
cteWithRow(Name,OrderCol)
as
(
Select Name, row_number() over(order by Name desc) from cteObjects
)
Select * from cteWithRow order by OrderCol
August 29, 2009 at 2:02 pm
Simply cast all the CASE fields as varbinary. This way, both numeric, date and strings will fit correctly to the CASE statement...
Also, in order to reverse, just use -1*value to reverse the field and then sort ASC
January 14, 2010 at 12:37 pm
The -1 multiply dont works when there are varchar columns. I tried and orderind desc results in a near random list
January 14, 2010 at 12:58 pm
How is the code being used, in a stored procedure?
January 14, 2010 at 3:14 pm
In my case nop. Just a trying some ideas to figure out how to make a static select using dynamic ordering in columns of different types (datetime, varchar, int) using asc and desc and avoiding RBAR. The cast to varbinary approach is a good shot but dont works with desc (the -1* cast(column as varbinary) just dont worked).
A shame u cannot use a alias column in the case statement.
January 21, 2010 at 7:28 pm
Rather than using complex CASE WHEN logic to generate your ORDER BY clause, why not use the following IF...ELSE logic?
declare @t table (c1 int, c2 varchar(50))
declare @sortorder int
insert @t values (1,'r1')
insert @t values (2,'h2')
insert @t values (3,'k3')
set @sortorder = 1
if @sortorder = 1
select c1, c2
from @t
order by c1 asc
else
select c1, c2
from @t
order by c2 desc
You could modify it by adding another variable @asc_desc to dynamically arrange the direction of sorting. See the following script:
declare @t table (c1 int, c2 varchar(50))
declare @sortorder int
declare @asc_desc int
insert @t values (1,'r1')
insert @t values (2,'h2')
insert @t values (3,'k3')
set @sortorder = 1
set @asc_desc = 1
if @sortorder = 1
begin
if @asc_desc = 1
select c1, c2
from @t
order by c1 asc
else
select c1, c2
from @t
order by c1 desc
end
else
begin
if @asc_desc = 1
select c1, c2
from @t
order by c2 asc
else
select c1, c2
from @t
order by c2 desc
end
Hope this helps.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply