June 5, 2015 at 6:29 am
kevriley (4/26/2012)
Wait a minute! What about that "ASC," after the END of the first CASE statement, above? If that CASE statement does not produce output, then wouldn't the four characters "ASC," simply be extra characters hanging out in the breeze? Wouldn't this cause a syntax error?
Answer: Yes they are extra characters. No they do not cause a syntax error because somehow CASE forgives this.
The reason that works is that the case evaluates to null, so you could write
select *
from Table
order by (select null) ASC
and that is valid SQL
+1 (and I'm quite surprised the author didn't seem to realize this behavior)
Gerald Britton, Pluralsight courses
June 5, 2015 at 7:25 am
ukarjee (4/26/2012)
Can dynamic sql help?
yep.
Edit. Jeez, did not see that this article was a re-run. No coffee yet this morning.
-- Itzik Ben-Gan 2001
June 5, 2015 at 8:49 am
[font="Comic Sans MS"]Though I could add a bit more info on how to use the CASE statement.
Note: the purpose of the following approach was to enable a user to dynamically sort a list of invoices to be printed in SSRS - a multi-valued parameter allowed pick-and-chose from the list. So this was not a high-volume "behind-the-scenes" but rather a user interaction to produce a report.
This was in an environment where both dynamic sql and stored procs are frowned upon and it was in SSQL2K8. At that time I was not familiar with the ROW-NUMBER function.
As a fix, I ended up fabricating the sorting fields - adding them to the main SELECT statement and ordering by the sorting fields. I could use the first sorting field for ASC, the second one for DESC. It is when I was fabricating the fields that I used the CASE statement to either select a fixed value (no impact on the sorting) or the field to be sorted. This was further complicated with multi-level options: InvoiceDate, Customer and InvoiceNumber, etc. So something like:[/font]
SELECT
CASE
WHEN @sortby = 'Invoice Number Up' THEN InvoiceNumber
ELSE 'X'
END AS bogusField1,
CASE
WHEN @sortby = 'Invoice Number Down' THEN InvoiceNumber
ELSE 'X'
END AS bogusField2,
CASE
WHEN @sortby = 'Customer Name and Invoice Number Down' THEN CustomerName
ELSE 'X'
END AS bogusField3,
FROM ...
ORDER BY bogusField1 ASC, bogusField2 DESC, bogusField3
[font="Comic Sans MS"]could end up with the equivalent of[/font]
ORDER BY InvoiceNumber ASC, 'X' DESC, 'X' ASC ...
[font="Comic Sans MS"]or[/font] ORDER BY 'X' ASC, InvoiceNumber DESC, 'X'
June 5, 2015 at 9:30 am
It occurs to me that the best way to *understand* what is actually happening with your sorts, is to simply include each of your sort-expressions, verbatim, in your SELECT field list while you are testing your code.
For example, if you have:
ORDER BY {Expression1} [ASC/DESC], {Expression2} [ASC/DESC], {Expression3} [ASC/DESC], (etc...)
[p][/p]
then include:
SELECT {Expression1}, {Expression2}, {Expression3} (...)
[p][/p]
and when you view the resultsets, it will be (or at least should be) completely obvious what is going on with the sorting and why. There are no mysteries here. There is nothing particularly special about the fact that the sort expressions involve CASE statements as opposed to any other kind of expression (such as those that might involve operators or function-calls).
I haven't read all the comments here, but there are numerous misconceptions in the original article about how SQL works.
June 5, 2015 at 9:37 am
I agree. The main misconception, I think, is that a CASE statement behaves differently when used in an ORDER BY clause. It doesn't.
June 5, 2015 at 9:48 am
Tony Palmeri (6/5/2015)
It occurs to me that the best way to *understand* what is actually happening with your sorts, is to simply include each of your sort-expressions, verbatim, in your SELECT field list while you are testing your code....
[font="Comic Sans MS"]Indeed. It just becomes a pain though when you want to combine different columns together such as DueDate, CustomerName, Amount, ... on top of wanting up or down. Each of the expressions tend to "compete" with each other, i.e. one exception might very well overrule the next one. There is a limit to understandability.
Sort of having T-SQL allowing the CASE statement *WITH* the ASC/DESC options as part of the THEN in the CASE itself, then ROW_NUMBER could be a second best choice.[/font]
June 5, 2015 at 9:48 am
SELECT ROW_NUMBER( ) OVER (ORDER BY
CASE @OrderBy
WHEN ... THEN ...
etc...
END
) AS RowNo
, other columns ...
FROM ...
WHERE ...
ORDER BY RowNo;
June 5, 2015 at 9:50 am
Just need to avoid using a CASE statement in a WHERE clause (or ON condition).
June 5, 2015 at 9:54 am
Henry B. Stinson (6/5/2015)
Just need to avoid using a CASE statement in a WHERE clause (or ON condition).
Why?
June 5, 2015 at 8:31 pm
After reading the article and the comments I was left wondering if the author and many of the commenters had a clear understanding of the difference between a statement, an expression and a clause in SQL whether a lot of the ensuing confusion could have been avoided.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2015 at 2:20 pm
I usually build the data via a CTE so I do not then need to worry about using case statements on the order by. But, good article, and interesting.
Regards;
Jeremy
June 6, 2015 at 3:10 pm
I was wrong on using the CTE suggestion after working it through. Since I usually avoid doing case statements on order by ordinarily. Here is a proposed alternative to consider:
DECLARE @sortType bit = 0;
If @sortType = 0
Begin
Select
Employee.EmpID,
Employee.EmpName,
Employee.EmpDOB
FROM Employee
Order by Employee.EmpDOB ASC
End
Else
Begin
Select
Employee.EmpID,
Employee.EmpName,
Employee.EmpDOB
FROM Employee
Order by Employee.EmpDOB DESC
End
June 6, 2015 at 4:33 pm
CASE in WHERE clause might cause optimizer to ignore indexes and in some cases might cause RBAR processing and/or full table scans, such as calling functions in a WHERE clause.
One should always test actual execution plans (not what SSMS predicts) on full size data sets to see if CASE in WHERE causes problems.
June 6, 2015 at 4:50 pm
Thanks, actually I always take a good luck at the optimizer plan, just trying to show an alternative to embedding case statements in the order by. But yes, if it clobbers performance, by all means, don't do it. Here is an interesting article from Brent Ozar, and another by Aaron Bertrand, respectively.
http://www.brentozar.com/archive/2014/01/dynamic-sorting
and another good example of conditional sorting without using a case on the order by -
http://sqlperformance.com/2012/08/t-sql-queries/conditional-order-by
June 6, 2015 at 6:47 pm
I just read the original article and wanted to add comment.
I believe if one wanted to "fix" the original SQL query
SELECT
Employee.EmpID,
Employee.EmpName,
Employee.EmpDOB
FROM Employee
ORDER BY
CASE
WHEN (@sortType = 0)
THEN @sortKey_A
ELSE @sortKey_D
END
-- one would use column names instead of @sortKey_A and @sortKey_B
-- instead of rewriting it, as I suggested in previous post, using:
SELECT ROW_NUMBER( ) OVER (ORDER BY CASE @SortOrder
WHEN ... ...
END ) AS rownum
, .....
, .....
FROM ...
WHERE ...
ORDER BY rownum
I've seen this work, including ASC and DESC, in code approved by one of the most demanding chief database architects I ever worked under
Both solutions are shown and discussed in an article by SQL Server guru Brent Ozar:
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply