I'm quite new to SQL Server. Now I came across a query like this:
我是SQL Server的新手。现在我遇到了这样的查询：
SELECT country FROM Hovercraft.Orders GROUP BY country ORDER BY ASC
There is no column name given in the order by clause. Is this possible? SSMS says no.
order by子句中没有给出列名。这可能吗？ SSMS说没有。
Jörg
约尔格
3 个解决方案
#1
10
It's probably a misprint - you have to specify what you are ordering by; this can be a column name, an expression or the number of a column in the output. It's most likely that the query you have seen was one of the latter, that simply omitted the column number 1 - like so:
这可能是一个印刷错误 - 您必须指定您所订购的产品;这可以是列名，表达式或输出中的列数。你看到的查询最有可能是后者之一，它只是省略了第1列 - 就像这样：
SELECT country FROM Hovercraft.Orders GROUP BY country ORDER BY 1 ASC
- so this would order by the contents of the first column of output (ie. country).
- 所以这将按第一列输出（即国家）的内容排序。
#2
1
I agree with @Mahmoud Gamal. But, also, it's possible to write such hack like this -
我同意@Mahmoud Gamal。但是，也可以这样编写这样的黑客 -
SELECT o.country, const_column = 1
FROM Hovercraft.Orders o
GROUP BY o.country
ORDER BY const_column ASC
In this case, sorting will be performed, but rows' order will not be changed.
在这种情况下，将执行排序，但不会更改行的顺序。
On MS SQL 2005:
在MS SQL 2005上：
On MS SQL 2012:
在MS SQL 2012上：
#3
0
This is not possible !..
这不可能 ！..
Order by clause always require column name or column number .
Order by子句总是需要列名或列号。
Would you please response me why you want this kind of situation , I think you are working with dynamic query or else please let me know.
你能否回答我为什么你想要这种情况，我认为你正在使用动态查询，否则请告诉我。
As per SQL Standard this not possible.
根据SQL标准，这是不可能的。
Thanks.
谢谢。
#1
10
It's probably a misprint - you have to specify what you are ordering by; this can be a column name, an expression or the number of a column in the output. It's most likely that the query you have seen was one of the latter, that simply omitted the column number 1 - like so:
这可能是一个印刷错误 - 您必须指定您所订购的产品;这可以是列名，表达式或输出中的列数。你看到的查询最有可能是后者之一，它只是省略了第1列 - 就像这样：
SELECT country FROM Hovercraft.Orders GROUP BY country ORDER BY 1 ASC
- so this would order by the contents of the first column of output (ie. country).
- 所以这将按第一列输出（即国家）的内容排序。
#2
1
I agree with @Mahmoud Gamal. But, also, it's possible to write such hack like this -
我同意@Mahmoud Gamal。但是，也可以这样编写这样的黑客 -
SELECT o.country, const_column = 1
FROM Hovercraft.Orders o
GROUP BY o.country
ORDER BY const_column ASC
In this case, sorting will be performed, but rows' order will not be changed.
在这种情况下，将执行排序，但不会更改行的顺序。
On MS SQL 2005:
在MS SQL 2005上：
On MS SQL 2012:
在MS SQL 2012上：
#3
0
This is not possible !..
这不可能 ！..
Order by clause always require column name or column number .
Order by子句总是需要列名或列号。
Would you please response me why you want this kind of situation , I think you are working with dynamic query or else please let me know.
你能否回答我为什么你想要这种情况，我认为你正在使用动态查询，否则请告诉我。
As per SQL Standard this not possible.
根据SQL标准，这是不可能的。
Thanks.
谢谢。