# Adding a ROW_NUMBER() with no column to ORDER BY?

Posted on

### Question :

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?

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: 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.

You can’t rely on your `INSERT` to generate `IDENTITY` values in the order of your original string. That may be what you observe, but that is just lucky coincidence and certainly not guaranteed.

The following will work if you don’t have duplicates:

``````DECLARE @str varchar(255) = 'one,two,three,four,five';

SELECT value, n = ROW_NUMBER() OVER
(ORDER BY CHARINDEX(',' + value + ',', ',' + @str + ','))
FROM STRING_SPLIT('one,two,three,four,five', ',')
ORDER BY n;
``````

For code golf maybe:

``````DECLARE @s char(99)='one,two,three,four,five';
SELECT value,n=RANK() OVER(ORDER BY CHARINDEX(','+value+',',','+@s+','))
FROM STRING_SPLIT('one,two,three,four,five', ',')ORDER BY n
``````

If you have duplicates it becomes a lot more complex. Some ideas here maybe:

Another option is to use a sequence:

``````DROP SEQUENCE IF EXISTS dbo.S;

SELECT value, NEXT VALUE FOR dbo.S
FROM STRING_SPLIT('one,two,three,four,five', ',');
``````

Output:

``````╔═══════╦═══╗
║ one   ║ 1 ║
║ two   ║ 2 ║
║ three ║ 3 ║
║ four  ║ 4 ║
║ five  ║ 5 ║
╚═══════╩═══╝
``````

It is annoying that `STRING_SPLIT` was implemented without a built-in numbering option. Vote for change at https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete

Just adding one more alternative from your question. But I think the ordering is not guaranteed,

``````SELECT value v,IDENTITY(INT,1,1) AS n
INTO #argg
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT * FROM #argg
``````