Can I depend on order of output when using row_number()?

If you want an ordered result set add an ORDER BY clause to your SELECT Period. Anything else is circumstantial and may or may not work depending on the current SQL build you're testing, the day's mood of the optimizer and the phase of Mars transit in Pisces A trivial example that contradicts your assumption: 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.

If you want an ordered result set, add an ORDER BY clause to your SELECT. Period. Anything else is circumstantial and may or may not work depending on the current SQL build you're testing, the day's mood of the optimizer and the phase of Mars transit in Pisces.

A trivial example that contradicts your assumption: 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.

1 My assumption was, "these two properties are not guaranteed absent an explicit order by clause" So you are not contratdicting my assumption, but you did provide an example I was looking for. – Shannon Severance Jul 10 '09 at 17:42 2 +1 Great example! – AlexKuznetsov Jul 10 '09 at 21:04.

Im struggling to find the relevance here; if you want explicit ordering the recommended way would be to use an ORDER BY clause in the query. I would never rely on the default ordering of a table when producing a query that I relied on the order of the results. Any modern RDBMS is going to be able to optimize an order by based on indexes and the like, so it's not something that has to be worried about.In regards to row_number, while it is a side effect that if no ORDER BY clause exists, the output is ordered by the ROW_NUMBER value, you cannot depend on this behavior, as it is not guaranteed.

Again, the only way to guarantee order of the output is with an ORDER BY clause.

Relevance is I want to be prepared with a counter example when working with other programmers who are relying on row_number() over (order by) to do the ordering, instead of calling it out in an order by clause. – Shannon Severance Jul 10 '09 at 17:56.

As mentioned, you can't rely on row order without an ORDER BY. However, you can rely on the ROW_NUMBER() function value. SELECT principal_id, name, ROW_NUMBER() OVER (ORDER BY principal_id DESC) AS DemoRank FROM msdb.sys.

Database_principals ORDER BY name If you consume the data in your client by DemoRank, then you would be OK, even without the ORDER BY clause If you rely on recordset ordering (ordinal index), then no. The example above gives first row (index = 0) as '##MS_PolicyEventProcessingLogin##', but using DemoRank value gives "db_denydatawriter" Basically, use ORDER BY.

If you want it ordered, you must use ORDER BY. Just look at the execution plan with SET SHOWPLAN_ALL ON if there is no "order by" in the StmtText column, you just get things however they are sorted after all the work is done. Sometimes you get lucky, sometime not, with how the data is stored/loaded/filtered/joined, etc.And how it is returned.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.