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

SQL Server Development (2000)

Dynamic Order By

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-04-27 : 01:51:38

Sorry for the cross post, but the Article discussion forum doesn't seem to get a whole lot of attention.

I was able successfully implement some of the ORDER BY code found here, but can't get it working completely right. I have two columns that I want to sort by, one is varchar, the other is int. I originally tried casting the int field to varchar, but then that wouldn't sort correctly(1, 14, 1570, 3, 35, etc...). I further tried casting the integer field, only if attempting to sort by the varchar field, but that wouldn't work. Oddly enough though, if I try to sort by the int field, it works fine without casting anything anywhere. The code is below, if anyone has any idea how to get it all sorting correctly it would be most appreciated.

Thanks
Steve


ALTER  PROCEDURE sp_SelectLinksByCategory
	(
	@CategoryID int,
	@Viewable bit = 1,
	@Approved bit = 1,
	@OrderBy varchar(25),
	@SortDir varchar(4)
	)
AS

SELECT
	LinkID,
	LinkCategoryID,
	LinkName,
	LinkURL,
	LinkClicks,
	IsApproved,
	IsViewable,
	CASE
		WHEN @OrderBy = 'LinkName' AND @SortDir = 'ASC' THEN LinkName
		WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'ASC' THEN LinkClicks
		ELSE NULL
	END AS SortASC,
	CASE
		WHEN @OrderBy = 'LinkName' AND @SortDir = 'DESC' THEN LinkName
		WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'DESC' THEN LinkClicks
		ELSE NULL
	END AS SortDESC	
FROM
	tblLinks
WHERE
	LinkCategoryID = @CategoryID
	AND IsApproved = @Approved
	AND IsViewable = @Viewable
ORDER BY 
	SortASC, SortDESC DESC

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2002-04-27 : 04:09:49

IFAIK, you can't use column aliases in the ORDER BY clause. Try using the ordinal position of the columns to sort on. In your example, use:

ORDER BY 8, 9 DESC

This should work.

Tim

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-04-27 : 09:18:34


SELECT
	LinkID,
	LinkCategoryID,
	LinkName,
	LinkURL,
	LinkClicks,
	IsApproved,
	IsViewable
FROM
	tblLinks
WHERE
	LinkCategoryID = @CategoryID
	AND IsApproved = @Approved
	AND IsViewable = @Viewable
ORDER BY CASE WHEN @OrderBy = 'LinkName' AND @SortDir = 'ASC' THEN LinkName END,
         CASE WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'ASC' THEN LinkClicks END,
	 CASE WHEN @OrderBy = 'LinkName' AND @SortDir = 'DESC' THEN LinkName END DESC,
	 CASE WHEN @OrderBy = 'LinkClicks' AND @SortDir = 'DESC' THEN LinkClicks END DESC

Depending on how your indexes are defined it may be more efficient to use dynamic sql.

timmy: Using column aliases in order by is allowed. In fact, the use of column ordinals is obsolete in SQL-99

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources