I need a new random number for each line in a sql query (server)

Question

I need a new random number for each line in a sql query (server)

Navigation

#1 by (3 votes)
#2 by (1 votes)

4

I use the sql server . I need to use a random number in several different columns (the same random number) but I want a new random number on each line ...

For example, let's assume that TABELAX has only 5 rows and I'll query the genre ...

select numeroRandom,numeroRandom2 from TABELAX

I want to get something like that.

| numeroRandom | numeroRandom2 |
|______________|_______________|
|    1         |      1        |
|    5         |      5        |
|    8         |      8        |
|    2         |      2        |
|    1         |      1        |

Please, somebody help me !!

sql random sql-server select

asked by anonymous 16.11.2016 / 11:43

2 answers

1

select 
       x.aleatorio as randomico1,
       x.aleatorio as randomico2
  from (select CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1  as aleatorio,
               tb.*
          from saluno tb) x
 order by x.ra

16.11.2016 / 13:33

Plot Main Title com Text and formula y = a + betaX + Epsilon What is the error in the while loop? moon language

score 3 · Accepted Answer

You can use the row_number function that assigns a row number based on aggregation or sorting, and use the newid function as sorting, which generates a unique value for each row every time query is executed:

select row_number() over(order by newid()) as randomico1,
       row_number() over(order by newid()) as randomico2,
       ra
  from saluno
 order by ra

The query above does not work using the top clause. If you need to use it, you must subquery with top before generating the random column as follows:

select row_number() over(order by newid()) as randomico1,
       row_number() over(order by newid()) as randomico2,
       x.ra
 from (select top(10) tb.*
         from saluno tb) x
order by x.ra

If you want to use the same value for another column more than once, you can also use subquery :

select x.randomico1,
       x.randomico1 as randomico2,
       x.ra
  from (select row_number() over(order by newid()) as randomico1,
               tb.*
          from saluno tb) x
 order by x.ra

Or:

select row_number() over(order by x.aleatorio) as randomico1,
       row_number() over(order by x.aleatorio) as randomico2,
       x.ra
  from (select newid() as aleatorio,
               tb.*
          from saluno tb) x
 order by x.ra

If you want a random number that is unrelated to the number of rows, use the following query where the number after % is the maximum:

select abs(checksum(newid()) % 10) + 1 as randomico1,
       ra
  from saluno

Or using for the two random columns:

select x.aleatorio as randomico1,
       x.aleatorio as randomico2
  from (select cast(rand(checksum(newid())) * 10 as int) + 1 as aleatorio,
               tb.*
          from saluno tb) x
 order by x.ra