-
Website
http://john-sheehan.com/blog -
Original page
http://john-sheehan.com/blog/index.php/slightly-more-dynamic-order-by-in-sql-server-2005/ -
Subscribe
All Comments -
Community
-
-
Top Commenters
-
Popular Threads
Almost, you can get the rank back without specifing it twice:
CREATE PROCEDURE CustomerGetFinal
@CustomerTypeID int
AS
BEGIN
SELECT
CustomerID,
FirstName,
LastName,
CustomerTypeID,
CASE
WHEN @CustomerTypeID = 1 THEN (RANK() OVER (ORDER BY FirstName, LastName))
WHEN @CustomerTypeID = 2 THEN (RANK() OVER (ORDER BY LastName, FirstName))
WHEN @CustomerTypeID = 3 THEN (RANK() OVER (ORDER BY LastName DESC, FirstName DESC))
END AS RankNumber
FROM
Customer
WHERE
CustomerTypeID = @CustomerTypeID
ORDER BY
RankNumber
END
I kind of thought that would be the case for larger queries. I'm only using this method on a query for under 100 rows so I haven't run into any performance issues yet.
About your sentence : "CASE kind of works, but its limited (only one column, no sort directions, datatype issues, etc)" I just want to say that you can control the direction with multiple CASE statements :
Order By
Case @orderby
when 'DATEASC' then StartDate
when 'NAMEASC' then LastName + FirstName
end ASC,
Case @orderby
when 'DATEDESC' then StartDate
when 'NAMEDESC' then LastName+ FirstName
end DESC
Also, I didn't run into any datatype problem in with the CASE, but I only tried with nvarchars and datetime, so I'm not sure about this.
fitCount is int
elect * from tFittest
declare @sortCriteria varchar(50) set @sortCriteria = 'name'
declare @sortDirection varchar(4) set @sortDirection = 'desc'
select fitCount, name, wwid, @sortCriteria, @sortDirection, cast(fitCount as sql_variant)
from tFittest
order by
case when @sortDirection = 'ASC' then
case when @sortCriteria = 'fitCount' then (cast(fitCount as sql_variant) )
when @sortCriteria = 'name' then (name )
end
end ASC,
case when @sortDirection = 'DESC' then
case when @sortCriteria = 'fitCount' then (cast(fitCount as sql_variant) )
when @sortCriteria = 'name' then (name )
end
end DESC
Just a quick question. Is it possible to write this dynamic Order By with DISTINCT, as I am getting error with DISTINCT in my select statement.
ORDER BY
CASE @AccountOperator
WHEN '>' THEN UserLastName
END ASC,
CASE @AccountOperator
WHEN '<' THEN UserFirstName
END DESC
when UserLastName is an alias?(without using sub query)
Everyone, please use this instead - it's faster when it counts:
ORDER BY
(CASE WHEN @CustomerTypeID = 1 THEN FirstName END),
(CASE WHEN @CustomerTypeID = 1 THEN LastName END),
(CASE WHEN @CustomerTypeID = 2 THEN LastName END),
(CASE WHEN @CustomerTypeID = 2 THEN FirstName END),
(CASE WHEN @CustomerTypeID = 3 THEN LastName END) DESC,
(CASE WHEN @CustomerTypeID = 3 THEN FirstName END) DESC
I ask that the author of this post (John Sheehan) try it and update this post with his findings if he finds that it works better. Thank you.
I can't take credit for this way of doing it as I found it months ago on another posting - but I can't remember where.
- Miguel_TX