Tricky dynamic ORDER BY - SQL Server Forums

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)

Tricky dynamic ORDER BY

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-05-18 : 18:08:20

I'm trying to do a dynamic order by to allow sorting based on different columns. So far so good. I've got it working, sort of.

However, the columns are different datatypes. For the code example below, the datatypes are:

title: varchar(250)
login_name: varchar(30)
added: smalldatetime
rating: numeric(5,3)
votes: int

 order by CASE @tiOrder
		WHEN 0 then title
		WHEN 2 then login_name
		WHEN 4 then convert(varchar(20),added,121)
		WHEN 6 then cast(rating as varchar(6))
		WHEN 8 then cast(votes as varchar(8))
	  END,
	  CASE @tiOrder
		WHEN 1 then title
		WHEN 3 then login_name 
		WHEN 5 then convert(varchar(20),added,121)
		WHEN 7 then cast(rating as varchar(6))
		WHEN 9 then cast(votes as varchar(8))
	  END desc

...as you can see, I'm converting them all to varchars, as otherwise SQL server barfs (it doesn't seem to like dynamic order by's with different datatypes). Everything is good, except the convert of the int and numeric datatypes messes up the sort; a sort order might be "10.0,3.0,4.0,8.0,9.0" for the numeric, and "1,103,2,3" for the int. It's obvious why that's happening, but how in the world do I fix it? Is there any way to force CONVERT to add leading zeros for int or decimal data?

Thanks
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-18 : 18:16:09

Use STR() instead of CAST or CONVERT for numbers. It left-pads them with spaces:

order by CASE @tiOrder
WHEN 0 then title
WHEN 2 then login_name
WHEN 4 then convert(varchar(20),added,121)
WHEN 6 then Str(rating,10,3)
WHEN 8 then Str(votes,10,3)
END,
CASE @tiOrder
WHEN 1 then title
WHEN 3 then login_name
WHEN 5 then convert(varchar(20),added,121)
WHEN 7 then str(rating,10,3)
WHEN 9 then str(votes,10,3)
END desc

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-05-18 : 19:18:39

Thank you! I wonder what other basic SQL commands I don't know

.

Cheers
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 19:59:30

another option

order by
CASE when @tiOrder = 0 then title else null end
CASE when @tiOrder = 1 then title else null end desc
CASE when @tiOrder = 2 then login_name else null end
CASE when @tiOrder = 3 then login_name else null end desc
CASE when @tiOrder = 4 then added else null end
CASE when @tiOrder = 5 then added else null end desc
CASE when @tiOrder = 6 then rating else null end
CASE when @tiOrder = 7 then rating else null end desc
CASE when @tiOrder = 8 then votes else null end
CASE when @tiOrder = 9 then votes else null end desc

instead of using str you can also

right(replicate('0',10)+convert(varchar(10),fld),10)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-18 : 20:29:19

And another option is to cast everything to VARBINARY(N). It ensures case sensitive ordering on text, hassle free date ordering and numerics...

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources