Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, May 9, 2012 8:02 PM
I actually found a forum entry similar to my question. It has to do with this line in Books Online Order By Clause Transact SQL. It says when describing the order_by_expression:
A sort column can include an expression, but when the database is in SQL Server 2005 (90) compatibility mode the expression cannot resolve to a constant.
So my question then is what exactly constitutes a constant? I ran across a query in some code I am reviewing, (yes I know, "what is an inline query doing in code?") which reads
select distinct sleno, len(sleno) from slebuno where len(sleno) = 11 or len(sleno) = 13 order by len(sleno), sleno desc
So I am confused as to why this works? Wouldn't the len function evaluate to an integer? Would an integer be considered a constant?
I ran across something similar at http://social.msdn.microsoft.com/forums/en-us/transactsql/thread/37AB5A18-E0DB-42BD-9733-77CED1C7D541 where George said that
According to the ANSI / ISO standards ORDER BY should not include expressions; only column names/aliases and column indexes are allowed.
But Hunchback replies with the quote from Books Online cited above and then states
The ORDER BY clause can include items that do not appear in the select list.
With which statement I agree but am still confused why a column that evaluates to an integer is interpreted as something other than the column index. (If you enter a literal integer, it would evaluate as clumn index and return index out of range.) So if anyone has an answer to this I would be glad to know.
Edward R. Joell MCSD MCDBA
Wednesday, May 9, 2012 8:37 PM ✅Answered
Yes, in "order by len(sleno)", len(sleno) does evaluate to an integer, but that's not an expression that evaluates to a constant. A constant would be something like order by 'ABC' or order by 17-5
Note that if you use a number (not an expression) in the order by, that refers to a column index, not a constant. So
Order By 3 - means order by the third column in the select list. (Note that IMO, this is not best practice, I would prefer using the column name)
Order By 2+1 - that's an error because you are ordering by a an expression that must evaluate to a constant
Order By Len(MyColumn) - legal because different rows may have different values of Len(MyColumn)
Tom
Wednesday, May 9, 2012 8:38 PM
Hi Edward,
Can you rephrase your question based on the following sample (2008)? Thanks.
SELECT ProductID, ProductName=Name, ListPrice, Color
FROM Production.Product
ORDER BY 1 DESC, 4 ASC
/* ProductID ProductName ListPrice Color
999 Road-750 Black, 52 539.99 Black
998 Road-750 Black, 48 539.99 Black
997 Road-750 Black, 44 539.99 Black
996 HL Bottom Bracket 121.49 NULL
995 ML Bottom Bracket 101.24 NULL ....*/
SELECT ProductID, ProductName=Name, ListPrice, Color
FROM Production.Product
ORDER BY 4 DESC, ProductID ASC
/* ProductID ProductName ListPrice Color
797 Road-550-W Yellow, 38 1120.49 Yellow
798 Road-550-W Yellow, 40 1120.49 Yellow
799 Road-550-W Yellow, 42 1120.49 Yellow
800 Road-550-W Yellow, 44 1120.49 Yellow...*/
SELECT ProductID, ProductName=Name, ListPrice, Color
FROM Production.Product
ORDER BY ProductNumber DESC, 4 ASC
/* ProductID ProductName ListPrice Color
870 Water Bottle - 30 oz. 4.99 NULL
864 Classic Vest, S 63.50 Blue
865 Classic Vest, M 63.50 Blue
866 Classic Vest, L 63.50 Blue ....*/
SELECT ProductID, ProductName=Name, ListPrice, Color
FROM Production.Product
ORDER BY LEN(CONVERT(varchar,ProductID)) DESC, 4 ASC
/* ProductID ProductName ListPrice Color
355 Guide Pulley 0.00 NULL
356 LL Grip Tape 0.00 NULL
357 ML Grip Tape 0.00 NULL
358 HL Grip Tape 0.00 NULL ...*/
SELECT ProductID, ProductName=Name, ListPrice, Color, LEN(ProductNumber)
FROM Production.Product
ORDER BY LEN(ProductNumber) DESC, 4 ASC
/* ProductID ProductName ListPrice Color (No column name)
680 HL Road Frame - Black, 58 1431.50 Black 10
722 LL Road Frame - Black, 58 337.22 Black 10
723 LL Road Frame - Black, 60 337.22 Black 10
724 LL Road Frame - Black, 62 337.22 Black 10....*/
SELECT ProductID, ProductName=Name, ListPrice, Color, LEN(ProductNumber)
FROM Production.Product
ORDER BY LEN(ProductNumber) DESC, LEN('LONDON') ASC
/* Msg 408, Level 16, State 1, Line 3
A constant expression was encountered in the ORDER BY list, position 2.
*/
SELECT ProductID, ProductName=Name, ListPrice, Color, LEN(ProductNumber)
FROM Production.Product
ORDER BY CASE WHEN ProductID % 2 = 0 THEN LEN(Name) ELSE LEN(ProductNumber)END DESC, 4 ASC
/* ProductID ProductName ListPrice Color (No column name)
904 ML Mountain Frame-W - Silver, 40 364.09 Silver 10
906 ML Mountain Frame-W - Silver, 46 364.09 Silver 10
942 ML Mountain Frame-W - Silver, 38 364.09 Silver 10
884 Short-Sleeve Classic Jersey, XL 53.99 Yellow 9.....*/
Article: http://www.sqlusa.com/bestpractices/order-by/
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012
Wednesday, May 9, 2012 9:23 PM
Naomi don't I get a chance to even look at the replies before you show them as answers.
Kalman; I was looking at your queries and their results and it does look like you are getting some results that I would not expect base in partiicular for column index 1 desc yet the result set show it as ascending. I don't have time right now to get more into it. I will look at it tomorrow.
Edward R. Joell MCSD MCDBA
Wednesday, May 9, 2012 9:34 PM
I am not marking them as answer, I am proposing an answer when I agree or like a content. When I propose an answer, it means I agree with it. I could have answered the same as Tom did.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, May 10, 2012 12:34 AM
joeller,
I could be wrong, but Tom's answer seems to be correct. You can not use a constant expression, like 'A' or 10-9, in the "order by" clause as the error msg clearly states.
If you do not agree with the proposition, you have the right to unpropose the post as the answer, but I find it polite and constructive to also leave a message about why you are unproposing it.
AMB
Some guidelines for posting questions...
Thursday, May 10, 2012 12:48 PM
Well it happens that I think it the best answer to my question although it does not address the inconsistent results that are being returned by queries that Kalman provided. (I would like to run them myself to determine if in fact those results are due to the data or the order by clauses).
My point is that I enter a question a 4:00 PM. Then when I check it before leaving at 5:00 it already has some one saying your question has been answered before I even have a chance to look at it. I think I sould be by default the one to determine if a reply answers my questions at least until a period of time has passed that indicates that I am not going to read the reply. Not a few minutes.
Naomi I do appreciate your prompt attention to all the questions that you address. (I have no idea where you find the time to do that and still do your work.) Maybe I am alone, but I would prefer a simple reply saying "concur" rather than marking it propose as answer before I see it. Many moderators have mentioned that when questions are marked as answered then people will not check them out. So even if a reply gives the main facts of the matter but leaves something out, if a forum viewer sees it proposed as answered they will not bother to reply even if they have an additional perspective on the issue. One of our other threads elicited some very interesting remarks from Erland and others that I found very valuable after I removed the "proposed as answered" marking. And I am sure that you have read threads with some very interesting replies submitted after the reply that was eventually marked as the answer that would have never be submitted otherwise.
In reference to queries provided by Kalman, the data provided in his resultsets are very interesting but too little is provided to come to any conclusions. I need to run similar queries with my own data to determine if there really is an issue or not. Given the press of priorities I will not be able to return to this until Monday. Meanwhile I want to leave the question open to give someone else a chance to address the issue.
Edward R. Joell MCSD MCDBA
Thursday, May 10, 2012 12:57 PM
I think you're missing the distinction between proposing an answer and marking an answer. Everyone has a right to propose an answer and this is my way of reading the thread, agreeing with the poster and making my opinion known. I have a right to do so and I see nothing wrong with proposing a good answer. I do also mark answers, but only if I come across a thread where I see several answers submitted in a close time frame and all correct. If I come across a wrong answer, I un-mark it.
Simple as that.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, May 10, 2012 2:12 PM | 1 vote
> My point is that I enter a question a 4:00 PM. Then when I check it before leaving at 5:00 it already has some one
> saying your question has been answered before I even have a chance to look at it
I think you are confused between proposing a post as the answer, with marking a post as the answer. I concur with you about the OP being the one to mark a post as the answer to her/his question, but I disagree about timing a post before we can propose a post as the answer. There is no wrong in doing so, and in case you have reasons or prove that the proposing should not be, then commenting the reasons will really help all of us.
> Many moderators have mentioned that when questions are marked as answered then people will not check them out. So even
> if a reply gives the main facts of the matter but leaves something out, if a forum viewer sees it proposed as answered they will not
> bother to reply even if they have an additional perspective on the issue.
If we really want to help the community, then it doesn't matter if a post have been already marked as the answer, in order to leave a comment or suggestion. As a reader, I do really find value in the comments placed after a post have been marked, no matter if it is to expand on the answer or to correct it.
Last but not less, there are no inconsistencies in Kalman's queries. Most of then are using a nonnegative integer constant to indicate the position of the column, from the columns list, to use for sorting. We all know that such practice is not a good one and should be avoided. On the other hand, the error you are concerned about is related to the use of constant expressions such:
- 'A' + 'B'
- (10 - 9)
- len(space(0))
As Tom already commented, using an expression as LEN(coln) will not yield a constant if you look at it from the point of view of the table, since this expression could return different values for each row.
Anyway, you have the right to choose which post better answer your question, but unproposing a post as the answer just because you need to wait till Monday to do some testing, doesn't seem right to me, in my opinion.
AMB
Some guidelines for posting questions...
Thursday, May 10, 2012 3:07 PM
Just an addition about ordering items - there is a great and one of my favorite blogs by Brad Schulz which is on the related topic:
http://bradsruminations.blogspot.com/2010/01/trolls-puzzle-sql-fable.html
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, May 17, 2012 12:59 PM
When I looked at Kalman's queries last week I could have sworn I saw an instance where the Order by called for desc but the data presented was asc. However, I do not see that now. I must have mistaken where he is doing the order by the len(convert(varchar, ProductID) desc for that. So in fact there are no inconsistencies. So I wonder why he wanted to include his issue in my question.
Having dealt with that I am satisfied with Tom's original answer. So I am giving him the credit.
And I still say I should be given time to abandon a question before a moderator come in and marks it as answered, Mr Wen. I have many work assignments so that monitoring a particular thread will in many cases have to take a back seat to other projects as determined by my COR. That does not mean I have neglected, ignored or forgotten it. (Meanwhile there are threads out there two, three, and five years old that have been properly answered, and proposed as answered by non-moderators that are still showing up as unanswered.)
Edward R. Joell MCSD MCDBA