Occasionally I have the problem that I need to run as query with a form of dynamic sorting – meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.
Now a CASE statement in the order by is often very effective for handeling such issues, but I’ve not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I’m just doing it wrong).
For example suppose we have a table which contains a “Date” field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work
DECLARE @ASC BIT SET @ASC = 1 SELECT * FROM DateTimeOrderTest ORDER BY DATE CASE @ASC WHEN 1 THEN ASC ELSE DESC END
But well – it doesn’t: “Incorrect syntax near the keyword ‘CASE’.”
Or one might think:
DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
CASE @ASC
WHEN 1 THEN Date ASC
ELSE Date DESC
END
Which gives a nice little: Incorrect syntax near the keyword ‘ASC’
A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer….oh, well), and multiply the factor to the order by.
This mean the following two queries give the same result:
SELECT * FROM DateTimeOrderTest ORDER BY CAST(Date AS INT) * -1
SELECT * FROM DateTimeOrderTest ORDER BY Date DESC
This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.
Anyways, I’ve found it quite useful myself.
Try to look at http://www.sqlteam.com/article/dynamic-order-by
The solution might be there.
The article linked is not handling the same issue as I mention here. The sql-team is about using a case to simply select between a number of fields.
However it doesn’t describe the dynamic usage of asc and desc in the same query, which was one of the main points of this one.
Case is a powerful utility, but not being able to use case for asc and desc makes it necessary to implement workarounds like this one.
DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
CASE @ASC
WHEN 1 THEN Date ASC
ELSE Date DESC
END
You have error in your query correct query is:
DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
CASE
WHEN @ASC=1 THEN (RANK() OVER (ORDER BY Date ASC))
WHEN @ASC=2 THEN (RANK() OVER (ORDER BY Date DESC))
END
Thank you for your comment.
Yes, I know the query has an error in it, because I also mention in the blog post, that it would give an "incorrect syntax near asc" error message. It is used for illustration.
The issue with your replacement query is that it is significantly slower (and much more complex) then just casting the datetime as an integer, and then multiplying either -1 or 1 to simulate the sorting direction.
Hi there thanks for the above code realy great but can you maby help me with somthing read the folwing code in ms sql 2005
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_system_Web_Tabel_Read_Last]
(
@Par_system_Table_Name VARCHAR(500)= af_17_12v_mon
)
AS
DECLARE @Value as varchar(500)
IF OBJECT_ID(@Par_system_Table_Name) IS NULL
RAISERROR(‘User table or view not found.’,16,1)
SELECT @Value = ‘SELECT TOP 1 * FROM ‘ + @Par_system_Table_Name(ORDER BY DateTime DESC)
exec(@Value
It gives me a esyntax error on order is there a difrent way to do this please email me on rynoeengelbrecht@webmail.co.za
Hi,
you can use the case statement like so:
ORDER BY
CASE WHEN @ASC = 1 THEN Date END ASC,
CASE WHEN @ASC <> 1 THEN Date END DESC
The ASC and DESC must come after the case statement
however this can still cause some issues if you wish to order by more than 1 field! – still haven’t worked that one out yet
Thanks for your input.
The "problem", so to speak, with your solution – outside the multiple fields one – is that you are "forced" to use multiple cases
But I hadn’t thought of using multiple cases in that manner, so it is worth a look at.
Thanks
Just wan to say thanks to tarek yehya for his solution. Until there’s a way to flip +/- on dates this is helpful.
Allan, thanks for the site (tusind tak)
Interesting posts, bookmarked for future referrence
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! I’m sure you had fun writing this article.
Matt > Thanks for an easy constructive way of solving this problem. Have tried on several occasions to find one without much luck!