This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Anyone can give any example of this situation ?
Actually I don't think it's a big problem . as I have seen so many codes are written like this
And also it said " A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant"?
what does it means ?
Anyone can give any example of this situation ?
Order by ordinal means to sort by column number, not by column name.
select *
from sys.objects
order by 8
Here the order is by column 8 = "create_date", as long as the column order don't change. Therefore you should always sort by column name, not by it's ordinal.
my concern is the following :
" A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant"?
what does it mean and impact.
Hi @sakuraime ,
> A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant. what does it means ?
Please check if this thread could help you.
Best regards,
Cathy Ji
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
And also it said " A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant"?
>
what does it means ?
>
What it says. This runs on SQL 2000
SELECT * FROM Orders ORDER BY 'nisse'
And, yes, that is a piece of nonsense, and it was a bug that it was accepted. In later versions, something like that yields an error.
As for ORDER BY 1, 2, 3, I believe this is defined in the ANSI standard. I tend to use it myself for ad hoc queries, but it is not very good in production code because of the maintenance issue it may cause.
i don't see any problem for select * from orders order by 'nisse'
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 30%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETH%3C/text%3E%3C/svg%3E)
Your 'col1' is being interpreted as col1, i.e., the column named col1. Try something else, a string that doesn't exist as a column in the the table. Example, the first fails, the second is ordered by LastName
SELECT * FROM Person.Person ORDER BY 'dummy'
SELECT * FROM Person.Person ORDER BY 'lastname'
ok... but who will write the stupid code like the 1st one, what's the intention to do that?
and how it's related to the statement
"A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant"?????
The fact that you can sort by a constant is stupid is why MS removed this in the first place! Why was it pu in the product? You'd have to ask Sybase this, when they designed the original SQL Server back in the 80's.
You can have a CASE in the ORDER BY, and for my example, imagine that CASE evaluating to the string 'dummy', and you end up with the same expression (after CASE evaluation as my first SELECT.
ok... but who will write the stupid code like the 1st one, what's the intention to do that?
I did say in my first response that it was a piece of nonsense, didn't I?
In my opinion, the fact that
SELECT col FROM tbl ORDER BY 'col'
is accepted is a bug. 'col' is a string literal; it is not an identifier. But I guess the story is this: Up to SQL 6.0, single and double quote both served as string literals on equal footing, and there was no general way to have identifiers like Order Details, since there was no way to delimit them.
However, there was an exception for column aliases, so that you could have spaces (or whatever) in them. And you could enclose them in single or double quotes, since they were equivalent. But then you may also want to sort by expressions in your SELECT list, so they also permitted it in the ORDER BY list. Again, single or double quotes - it was all the same.
And it still works for backwards compatibility.
"A sort column can include an expression , but when the database is in SQL COMPATIBILITY 90 or higher , the expression cannot resolve to a constant"?????
Code like this:
DROP TABLE plantig
go
CREATE TABLE plantig (a int not null, b int not null primary key)
go
INSERT plantig VALUES(8, 2)
INSERT plantig VALUES(7, 1)
INSERT plantig VALUES(6, 3)
go
SELECT 'half' = a/2 , b
FROM plantig
ORDER BY 'half', 5+5
Yes, I was able to run this code successfully on SQL 6.5.
I could even have:
ORDER BY 'ha' + 'lf', 5+5
It ran with out errors. It did sort by a/2, though.
And again, it is a piece of nonsense that should not be accepted. But even if it is nonsense, there were surely people who wrote like that. Not on purpose, but because they were sleepy distracted etc. So all of a sudden raising errors for it was a breaking change.