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?
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:
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;
CREATE SEQUENCE dbo.S START WITH 1;
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