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)

dynamic order by

Author

Topic

rico_bano
Starting Member

35 Posts

Posted - 2007-04-23 : 10:04:52

im tying to order a result set by using a case statement however i have run into a small problem. the code is below

when @orderBy = 1 then classification1, [date], title, type asc
when @orderBy = 2 then classification1 desc, [date], title, type asc
when @orderBy = 3 then classification2 [date], title, type asc
when @orderBy = 4 then classification2 desc, [date], title, type asc
when @orderBy = 5 then [date] classification1, title, type asc
when @orderBy = 6 then [date] desc, classification1, title, type asc
when @orderBy = 7 then title, classification1, [date], type asc
when @orderBy = 8 then title desc, classification1, [date], type asc
when @orderBy = 9 then type,classification1, [date], title asc
when @orderBy = 10 then type desc,classification1, [date], title asc

im getting a syntax error on the first line on the first comma.

Any ideas

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:11:38

You can't do that.

You have to use CASE WHEN for each of the column in the ORDER LIST. And you have to ensure that the datatype is same or you have to convert from one to another.

Note : The code will not be pretty.


case @orderby 
when 1 then classification1
when 2 then classification1
when 3 then classification2
...
when 10 then type
end,
case @orderby
when 1 then [date]
when 2 then [date]
...
end

rico_bano
Starting Member

35 Posts

Posted - 2007-04-23 : 10:14:53

eishhhhh that looks messy any ideas how i can dynamically order my results in a better way. Ive tried creating a sql string and executing that but my statement is big and it become very difficult

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:17:16

Dynamic SQL http://www.sommarskog.se/dynamic_sql.html

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-23 : 10:28:30

Better yet, if you can move the order by to your front end, move it.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:34:28

quote:
Originally posted by dinakar

Better yet, if you can move the order by to your front end, move it.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Good idea

rico_bano
Starting Member

35 Posts

Posted - 2007-04-23 : 10:55:41

front end may be an option, but im using pagination and would require a lot of re - working.

This one has me totally stumped

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-23 : 10:58:27

Move to SQL Server 2005.
There you have the ROW_NUMBER() function with PARTITION and inline ORDER BY.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-23 : 10:59:07

then take a look at the link i posted.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-23 : 13:54:07

quote:
Originally posted by Peso

Move to SQL Server 2005.
There you have the ROW_NUMBER() function with PARTITION and inline ORDER BY.

Peter Larsson
Helsingborg, Sweden

Peter, do you have a "good" way to do a dynamic order by in 2005? I was just messing around, semi-based on OP question and hacked out a quick order by:

DECLARE @T TABLE(a int, b int, c int)

INSERT @T
SELECT 1, 6, 8
UNION SELECT 2, 5, 9
UNION SELECT 3, 4, 7
UNION SELECT 8, 1, 7

DECLARE @OrderBy INT
SET @OrderBy = 4


SELECT *, 
	CASE
		WHEN @OrderBy = 2 THEN ROW_NUMBER() OVER(ORDER BY b) 
		WHEN @OrderBy = 3 THEN ROW_NUMBER() OVER(ORDER BY c) 
		WHEN @OrderBy = 4 THEN ROW_NUMBER() OVER(ORDER BY c, b) 
		WHEN @OrderBy = 5 THEN ROW_NUMBER() OVER(ORDER BY c, a) 
		--- etc..
		ELSE ROW_NUMBER() OVER(ORDER BY a ASC)
	END AS RowNumber
FROM @T
ORDER BY RowNumber

But, I'm always looking for a better way..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources