Monday, February 4, 2013

SQL Code Snippets - Randomly Ordered Row Number

Sometimes you just want to generate a row number for records that is randomly ordered.

RecordOrder, RowOrder
1,2
2,4
3,1
4,3

Or something like that.  You want nothing fancy, nothing to base the order on, no groupings.  Just a simple, straight forward method for order

     ,row_number() over(partition by 1 order by newid() asc) row_num

Example from my ExampleETL database:



id
name
row_num
4.47E+08
sysssislog
7
6.71E+08
SSIS Configurations
2
6.87E+08
FILELIST
1
7.35E+08
PERSON
8
7.99E+08
COMPANY
3
8.47E+08
COMPANYPERSONRELATIONSHIP
10
8.79E+08
COMPANYPERSON
4
9.59E+08
ExampleDatabaseToDatabaseTransfer
9
9.75E+08
PRODUCT
6
1.02E+09
PRODUCTCOMPANYPRICE
5


Note that the row_num assigned was within the range requested, and assigned randomly, it should provide different results when run repeatedly.


No comments:

Post a Comment