3

Other then creating a temp table is there an elegant way to select a random column value in an inline query

SELECT [Col1],
       [Col2],
       ChooseRandomlyFrom('Lateral', 'AP', 'AP Ext Rot', 'PA', 'PA Obl', 'PA Pbl Int Rot', 'Lateral', 'L5 S1', 'PA Navicular'),
       [Col3]
FROM [dbo].[MyTable]

I would like random per row in a query to generate a sample data set

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
bumble_bee_tuna
  • 3,533
  • 7
  • 43
  • 83
  • 1
    Dynamic SQL: construct the statement as a string (using a CASE on a random int to pick the random column) then use sp_executeSQL to execute it. – Alex K. Sep 25 '17 at 13:06
  • 1
    The problem with things like `SELECT CASE CRYPT_GEN_RANDOM(4) % 2 WHEN 0 THEN 'Foo' WHEN 1 THEN 'Bar' END` will be that the expression gets expanded out to `SELECT CASE WHEN CRYPT_GEN_RANDOM(4) % 2 = 0 THEN 'Foo' ELSE CASE WHEN CRYPT_GEN_RANDOM(4) % 2 =1 THEN 'Bar' ELSE NULL END END` so the random number is evaluated multiple times not fixed. – Martin Smith Sep 25 '17 at 13:09
  • 1
    Elegant? use a table valued function pass in array of values and let the function randomly select a value? or https://stackoverflow.com/questions/30200105/sql-server-2012-random-string-from-a-list? – xQbert Sep 25 '17 at 13:09
  • 1
    Do you want random per row or random per select value? – nimdil Sep 25 '17 at 13:11
  • 1
    @nimdil I would like random per row in a query to generate a sample data set – bumble_bee_tuna Sep 25 '17 at 13:14
  • 1
    There's no *elegant* way to do that. I can think of clunky ways (tack on a `ROW_NUMBER() OVER (ORDER BY NEWID())` on both tables, fix things up with modulo), but no elegant ways. – Jeroen Mostert Sep 25 '17 at 13:18
  • @xQbert Will the CTE work on a per Row Basis ? – bumble_bee_tuna Sep 25 '17 at 13:21
  • Check an updated answer. – Stanislovas Kalašnikovas Sep 25 '17 at 13:49

2 Answers2

2

You can achieve it by using a variable with CASE expression in following:

DECLARE @rand INT
SET @rand = ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 

SELECT [Col1],
       [Col2],
       CASE @rand 
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        ELSE 'D'
       END AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]

Or you could achieve it without variable in following:

SELECT [Col1],
       [Col2],
       CASE ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        ELSE 'D'
       END AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • 2
    That's per-table, not per-row. – Jeroen Mostert Sep 25 '17 at 13:17
  • I believe this is still per Table – bumble_bee_tuna Sep 25 '17 at 13:23
  • @JeroenMostert missed what OP want, now I got it, updated answer. – Stanislovas Kalašnikovas Sep 25 '17 at 13:43
  • try running `SELECT CASE ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END AS RandColValue into #t1 FROM master..spt_values` and then `SELECT COUNT(*), RandColValue FROM #t1 GROUP BY RandColValue` – Martin Smith Sep 25 '17 at 13:50
  • @MartinSmith & Stanis Both of these kind of work but some of the rows return NULL, why is that ? – bumble_bee_tuna Sep 25 '17 at 13:53
  • This is the issue I pointed out ages ago in the 2nd comment on the Q. I was hoping potential Answerers would avoid making this mistake but clearly not. – Martin Smith Sep 25 '17 at 13:57
  • Fun Times with T-SQL ! Thanks Guys I think this is the closest to an answer given the original question. The Solution atleast for my purposes is just to add an ELSE 'DEFAULT' to avoid the NULL issue. Thanks for the help – bumble_bee_tuna Sep 25 '17 at 14:00
  • 1
    Note that the distribution of "random" values you will be heavily skewed towards the last case (covered by `NULL`), and the other values won't be equally distributed either. If you don't care about that for testing purposes, great, but do not make the mistake of thinking your data is representative of a normal distribution. – Jeroen Mostert Sep 25 '17 at 14:02
  • @JeroenMostert Good Point For my Purposes it is ok but otherwise would be an issue the distribution is definitely not uniform – bumble_bee_tuna Sep 25 '17 at 14:31
1

You can achieve it by using a variable with Choose function

DECLARE @rand INT
SET @rand = ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 

SELECT [Col1],
       [Col2],
       Choose(@rand,'A','B','C','D') AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]
TT.
  • 15,774
  • 6
  • 47
  • 88
rahul c
  • 57
  • 8