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

Question

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 for each row...

for example, let’s assume that TABELAX has only 5 lines and I make a query of the genre...

select numeroRandom,numeroRandom2 from TABELAX

i want to get as a result something like..

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

Please, somebody help me!!

Answer 1

You can use the function row_number assigning a line number based on aggregation or ordering, and using the function newid as sort, which generates a unique value for each line each time the 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

To query above does not work using the clause top. If it is necessary to use it, a subquery with the 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 more than once the same value for another column you can also use one 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 lines, use the following query where the number after the %is the most:

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 

Answer 2

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