ORDER BY in section - SELECT keyword

Question

See below DDL:

create table #names (name varchar(20), Gender char(1))
    insert into #names VALUES ('Ian', 'M')
    insert into #names values ('Marie', 'F')
    insert into #names values ('andy', 'F')
    insert into #names values ('karen', 'F')

and SQL below:

select row_number() over (order by (select null)) from #names

This adds a unique number to each line. I could also do this (which does not add a unique string):

select row_number() over (partition by gender order by (name)) from #names

Why don't you need a "SELECT name" but you don't need a SELECT null?

Answer 1

As far as I can tell, this is just a SQL Server quirk. SQL Server does not allow constants in ORDER BY(and not in GROUP BY, which may occur in other contexts).

Probably the source of this is the sentence ORDER BYin the instruction SELECT:

ORDER BY 1

"1" - , . , ( ), . , ORDER BY 2 + 1 ? ? 3?

, Windows. , , . :

ROW_NUMBER() ORDER BY (CASE WHEN NAME = NULL THEN 'Never Happens' ELSE 'Always' END)

, . = NULL true, . SELECT NULL.

Answer 2

Order By 4 .

• ,
• ,

MSDN.

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

, SELECT NULL, , order by, , # 4, . COUNT (*) .

, Name Group order by, , , # 1. . , .

select row_number() over (order by (select null)),name from #names

select row_number() over (order by name),name from #names

select row_number() over (order by Gender),name from #names

.

, , , . , .

, SQL Server Order By, , , , №4 "SELECT name" . , Order By.