Adding ROW_NUMBER() Without ORDER BY in SQL Server

order-bysql serversql-server-2017window functions

So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order.

Let's say my source data is:

SELECT value
FROM STRING_SPLIT('one,two,three,four,five', ',')

which returns the items in the original (desired) order:

value
-----
one
two
three
four
five

If I try to use ROW_NUMBER() or RANK() I'm forced to specify an ORDER BY, for which value is the only legal choice:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY value)
FROM STRING_SPLIT('one,two,three,four,five',',')

But this (as expected) sorts value alphabetically instead of leaving it in the desired original order: 

value   n
------ ---
five    1
four    2
one     3
three   4
two     5

Joining to a number table doesn't work, since without a WHERE clause I'll get a full outer join.

About the best I could come up with was using a temp table with an identity field:

CREATE TABLE #argg (n INT IDENTITY(1,1), v VARCHAR(99))

INSERT #argg 
SELECT value v
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT *
FROM #argg

DROP TABLE #argg

but this is really long and annoying. Any better ideas?

Best Answer

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this:

SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')

It works for the simple case you posted in the question:

enter image description here

I should say that there is not a documented guarantee that the ROW_NUMBER() value will be in the precise order that you expect. But it's code golf, so this seems good enough.

Related Question