Geek Answers Handbook

Can I depend on the output order when using row_number ()

I believe the answer is no . And I am looking for an example counter to show that the order of output is not guaranteed, without an order by clause.

consider:

create table #order (orderId int primary key clustered
    , customerId int not null -- references customer(customerId)
    , orderDateTIme datetime not null)

insert into #order values (1, 100, '2009-01-01')
insert into #order values (2, 101, '2009-01-02')
insert into #order values (3, 102, '2009-01-03')
insert into #order values (4, 103, '2009-01-04')
insert into #order values (5, 100, '2009-01-05')
insert into #order values (6, 101, '2009-01-06')
insert into #order values (7, 101, '2009-01-07')
insert into #order values (8, 103, '2009-01-08')
insert into #order values (9, 105, '2009-01-09')
insert into #order values (10, 100, '2009-01-10')
insert into #order values (11, 101, '2009-01-11')
insert into #order values (12, 102, '2009-01-12')
insert into #order values (13, 103, '2009-01-13')
insert into #order values (14, 100, '2009-01-14')
insert into #order values (15, 100, '2009-01-15')
insert into #order values (16, 101, '2009-01-16')
insert into #order values (17, 102, '2009-01-17')
insert into #order values (18, 101, '2009-01-18')
insert into #order values (19, 100, '2009-01-19')
insert into #order values (20, 101, '2009-01-20')

select * from #order
-- Results in PK order due to clustered primary key

select orderId, CustomerId, orderDateTime
    , row_number() over (partition by customerId order by orderDateTime) RN
from #order

In MS SQL Server 2005, output ordering has two properties:

  • Lines for each customerIdare adjacent at the output.

  • Row_number() is consistent within each customer.

, , . , , order by, , MS SQL Server . , .. , .

, , , , , .

+3
4

, ORDER BY SELECT. . SQL, , .

, :

select orderId, CustomerId, orderDateTime
    , row_number() over (partition by customerId order by orderDateTime) RN
    , row_number() over (partition by orderDateTime order by customerId) AntiRN
from #order
+10

; , - ORDER BY .

, . .., , .

row_number, , ORDER BY , ROW_NUMBER, , .

, - ORDER BY.

+2

, ORDER BY.

ROW_NUMBER() .

SELECT
    principal_id, name, 
    ROW_NUMBER() OVER (ORDER BY principal_id DESC) AS DemoRank
FROM
    msdb.sys.database_principals
ORDER BY
    name

DemoRank, , ORDER BY

( ), .

(index = 0) "##MS_PolicyEventProcessingLogin##", DemoRank "db_denydatawriter"

, ORDER BY.

+1

, ORDER BY.

SET SHOWPLAN_ALL ON

StmtText "", , . , - , , /// .. .

0
source

More articles:


All Articles