dynamic order by with multiple columns

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

dynamic order by with multiple columns

Author

Topic

kasabb
Starting Member

8 Posts

Posted - 2007-05-08 : 16:07:27

Below is what I am using to dynamically sort on different columns. Here are the 4 records I get back ...

JOHN SMITH 2007 2007-04-10 00:00:00.000
JOHN SMITH 2007 2007-02-06 00:00:00.000
JOHN SMITH 2007 2007-01-09 00:00:00.000
JOHN SMITH 2007 2007-03-13 00:00:00.000

The problem is that it should return sorted as follows - with January being the first record.

JOHN SMITH 2007 2007-01-09 00:00:00.000
JOHN SMITH 2007 2007-02-06 00:00:00.000
JOHN SMITH 2007 2007-03-13 00:00:00.000
JOHN SMITH 2007 2007-04-10 00:00:00.000

But when I do not put the cast on fldDt1 I get "Syntax error converting datetime from character string". Here is my code.


declare @ORDERBY nvarchar(10)
set @ORDERBY = 'sort1'
select fldName, fldYr, fldDt1 
from table1
order by

case when charindex(rtrim(@ORDERBY), ',sort1,sort2,') > 0 then
            case @ORDERBY
                        WHEN 'sort1' THEN fldName + ',' + fldYr + ',' + cast(fldDt1 as varchar(25))
                        ...
                        ...
            else

                        cast(fldDt1 as varchar(25)) + ',' + fldName + ',' + cast(fldYr as char(4))
            end
end

herothecat
Starting Member

19 Posts

Posted - 2007-05-08 : 22:08:04

Why not do this ....

declare @ORDERBY nvarchar(10)
set @ORDERBY = 'sort1'
select fldName, fldYr, fldDt1
from table1
order by

case when charindex(rtrim(@ORDERBY), ',sort1,sort2,') > 0 then
case @ORDERBY
WHEN 'sort1' THEN fldName, fldYr , fldDt1
...
...
else
fldDt1 , fldName, fldYr
end
end

Why push the envelope when you can just open it?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-08 : 23:35:49

quote:
Originally posted by herothecat

Why not do this ....

declare @ORDERBY nvarchar(10)
set @ORDERBY = 'sort1'
select fldName, fldYr, fldDt1
from table1
order by

case when charindex(rtrim(@ORDERBY), ',sort1,sort2,') > 0 then
case @ORDERBY
WHEN 'sort1' THEN fldName, fldYr , fldDt1
...
...
else
fldDt1 , fldName, fldYr
end
end

Why push the envelope when you can just open it?

The THEN clause of you code is invalid, bacause a CASE statement must return a single value, not a series of columns.

CODO ERGO SUM

herothecat
Starting Member

19 Posts

Posted - 2007-05-09 : 00:05:37

Just a thought... :-)

Why push the envelope when you can just open it?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-09 : 00:26:44

declare @tt table (fldName varchar(50), fldty int, flddt1 datetime)
insert @tt(fldName, fldty, flddt1)
select 'JOHN ', 2007, '2007-04-10 00:00:00.000' union all
select 'Raja', 2006, '2007-02-06 00:00:00.000' union all
select 'Arun', 2005, '2007-01-09 00:00:00.000' union all
select 'Balaji', 2007, '2007-03-13 00:00:00.000'

declare @orderby varchar(20)
Set @orderby = 'sort2'

Select fldName,
fldty,
flddt1
From @tt
Order by --fldName, fldty, flddt1
Case when @orderby = 'sort1' then fldName end,
Case when @orderby = 'sort1' then fldty end,
Case when @orderby = 'sort1' then flddt1 end,
Case when @orderby <> 'sort1' then flddt1 end,
Case when @orderby <> 'sort1' then fldName end,
Case when @orderby <> 'sort1' then fldty end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources