patternsqlModerate
Adding a ROW_NUMBER() with no column to ORDER BY?
Viewed 0 times
ordercolumnwithaddingrow_number
Problem
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:
which returns the items in the original (desired) order:
If I try to use
But this (as expected) sorts
Joining to a number table doesn't work, since without a
About the best I could come up with was using a temp table with an identity field:
but this is really long and annoying. Any better ideas?
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
fiveIf 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 5Joining 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 #arggbut this is really long and annoying. Any better ideas?
Solution
The canonical way to do this is the following:
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() 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.Code Snippets
SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')Context
StackExchange Database Administrators Q#221290, answer score: 13
Revisions (0)
No revisions yet.