I think that the strong relation between ORDER BY and TABLE colums (one-to-one) is only the question of internal ORDER BY statement realisation with unknown reasons :)

declare @t table (id int, names varchar(100))
insert into @t select 1,'name1' union all select 2,'name2'

select id,id from @t order by id
--error
select id,id1 from @t order by id
--ok
select id from @t order by id,id1
--ok
select id from @t order by id,id1,id1
--error
select id from @t order by id,id*1,id/1
--ok

limitation of group by clause is to have the column in select list, and that condition is satisfied so the statement runs successfully is my guess

Blockquote

Its a run-time error, however the sql can be parsed.

When at runtime for ORDER BY, the parser gets the ordinal position of the column specified. If the ordinal positions are repeating, the runtime error occurs. If you are naming id as "id1" as alias and using that, the ordinal position gets changed and executing it fine.

It can cause an invalid memory access when the specification contains two or more duplicate ORDER BY columns.No such problem for GROUP BY

@uzairahm If you're saying that GROUP BY expressions must be in the SELECT clause, that is completely incorrect. I have had several queries where I did not need one of the grouped expressions in the select clause and SQL Server happily accepts that.

I have came accross the same question at Microsoft TechNet Forum and here is an answer to that;

The reason which i know why Duplicate Column Names are not allowed in ORDER BY Cluase is pretty simple;

Please see the below SQL what you think it might generate as output, if SQL allows this kind of syntax it will obviously very hard for optimizer to sort which thing to do. To avoid such cases MS SQL doesn't allow Duplicate Column Names.

All Columns appeared in the ORDER BY Cluase must be unique. Similarly you can't referrence a Column Name by its position in the SELECT list if its Column Name is appearing in the ORDER BY Clause.

Both SQL statement which i have mentioned below are incorrect in syntax.

select id from #t order by id,id desc

select names ,id from #t order by 2,id

Please let me know if this doesn’t work for you. Hope I have answered you correctly. Also i have put these details on MSDN Library so others users can benefit from it; Take a look at below;

http://msdn.microsoft.com/en-us/library/ms188385.aspx

Thanks, Hasham

In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. The value must be unique among all columns listed in the SELECT statement as it has to determine the sequence of the sort columns in the ORDER BY clause for the organization of the sorted result set.

I have tried this query without getting any error....

Select id From #t Order By id,Case When id>0 Then id Else id End

First and foremost, the difference is in the functionality of the two clauses. GROUP BY is used to group or aggregate values whereas the ORDER BY is used to order the result set. Ordering cannot be done on the same column twice - that is why ORDER BY returns an error.

GROUP BY allows us to use duplicate GROUPING SETS, which is why the use of the same expression (in this case, a column) twice in the GROUP BY is perfectly legal.

An ORDER BY clasue changes the order in which result are returned whereas a GROUP BY clause has no effect on the order in which results are returned. Therefore, if ducplicate columns exist within an ORDER BY clause, SQL Server is unable to determine the correct order of a result set.

Yes you can use GROUPING SETS in a GROUP BY clause however I'm pretty sure that the syntax you need to use is GROUP BY GROUPING SETS (.....) which means that the GROUP BY statement in this question does not use GROUPING SETS. I could be wrong though :-)

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

also if Order By allow duplicate, some will ask it to sort the first into ASC and the second to DESC, in that case what SQL Server do?????

Think about what this means: ORDER BY foobar ASC, foobar DESC

Sequences are not sets; duplicates can matter.