complex ORDER BY
-
I have a result set coming back that needs to be ordered 2 different ways. Primarily it needs to be ordered by time (descending) and the description that accompanies it needs to be ordered ascending. Normally this is easily done with ORDER BY col1 DESC, col2 ASC....but there is a hitch.
The order by field is passed as a variable and used in a case statement. Which makes things more complex....so for the query:
select TimeCol, DescriptionCol
from myTbl
case @sortBy
when 'Time' then convert(varchar(50),TimeCol,121)
when 'Description' then DescriptionCol
end
Now when 'Time' is the parameter I need to have the order by evaluate to TimeCol DESC, DescriptionCol ASC and vice versa for when 'Description' is passed.
Any ideas?
-
Do you mean
select TimeCol, DescriptionCol
from myTbl
order by
case @sortBy
when 'Time' then convert(varchar(50),TimeCol,121)
when 'Description' then DescriptionCol
end DESC,
case @sortBy
when 'Time' then DescriptionCol
when 'Description' then convert(varchar(50),TimeCol,121)
end ASC
Far away is close at hand in the images of elsewhere.
Anon. -
Well, the syntax for that doesn't check out. Incorrect syntax near CASE. I'm guessing that the comma is causing it.
But regardless, that would only work for Time. If Description was selected, that logic would resolve to ORDER BY Description DESC, Time ASC and what I need in that case is Description ASC, Time DESC.
-
You probably need to use Dynamic SQL. I've assigned a value to @sortBy for illustrative purposes, but I assume that this value will enter a procedure as a parameter:
Declare @sortBy varchar(50)
Declare @sql varchar(100)
Declare @sql_piece varchar(50)
set @sortBy = 'Description'
If @sortBy = 'Time'
Begin
set @sql_piece = 'TimeCol DESC, DescriptionCol ASC'
End
Else If @sortBy = 'Description'
Begin
set @sql_piece = 'DescriptionCol DESC, TimeCol ASC'
End
set @sql = 'select TimeCol, DescriptionCol from myTbl order by ' + @sql_piece
EXEC (@sql)
-
Dynamic sql really isn't a good option because of the overhead coupled with the use of this query. The benefit is in the cached query plan and dynamic sql blows that away. If I can't do this inherently then I'll probably have to build the string in code....which I'm really not wanting to do either for other reasons....
-
I don't have SQL Server available as I write this but my approach would be to do a DATEDIFF between a fixed date in the future and your time col then convert the result to text before the sort.
As your time gets further into the future the DATEDIFF gets smaller so you get a defacto DESC sort order.
-
I actually wanted to do that but I run into problems because both the time and description must be casted into the same datatype or the order by clause blows up...and when you convert the datediff'd value into a varchar, the sorting is handled differently....for example:
Using datediff without the convert:
-11226
-11645
-11689
-11751
-11953
-253805
-253923
-253992
-2351809
-2352338
-2352346
-2490637
-2490637
-2663437
-16746637
-116193037
-116193037
-189028237
This returns correctly....
Then with the convert to align datatypes:
-11285
-116193096
-116193096
-11704
-11748
-11810
-12012
-16746696
-189028296
-2351868
-2352397
-2352405
-2490696
-2490696
-253864
-253982
-254051
-2663496
-
I take your point but DATTIME fields can hold values up to 31-Dec-9999. If you use a date that ridiculously far in the future then you DATEDIFF should always return a value with the same number of digits.
-
well...as long as the app isn't in use after the year 2060 I think this should do it.
Cool. Thanks a bunch!
-
Depending on what you mean by "vice versa", there may or may not be a better solution. Please could you post precisely what the sorting should be for every value of the parameter @sortBy? All of the following can IMHO in a way be considered as "vice versa" to TimeCol DESC, DescriptionCol ASC
ORDER BY DescriptionCol ASC, TimeCol DESC
ORDER BY DescriptionCol DESC, TimeCol ASC
ORDER BY TimeCol ASC, DescriptionCol DESC
EDIT : Sorry, I seem to be partially blind.. just found the answer in one of your replies. Unfortunately, it also means that I won't be able to come up with a better solution
. -
Well, the syntax for that doesn't check out. Incorrect syntax near CASE Don't see why, it worked fine when I tested it
Can you please post your final solution, I am interested in how it compares with your original post
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply