Create tables randomly

Asked

Viewed 162 times

2

My question is this::

I have a table containing 25 records of people’s names. But I need to divide it into 5 groups of 5 people randomly.

Being a table 'Group A' with 5 people, another table 'Group B' with 5 people and so on.

It needs to be in SQL Server, because the data mass is much larger than this. Does anyone know how to use the function rand()?

I put it like this: 

select * from pessoas order by RAND()

But as I put, for example, to separate into groups of 5?

sql database sql-server

  • Is there a specific need for this to be done in SQL Server?

  • 2

    Welcome to SOPT. An interesting thing to read: http://meta.pt.stackoverflow.com/questions/699/faq-da-community . As for your question, have you tried anything? If yes, edit your question with the code you tried, it is better to help you. Another thing, why do you want to separate in other tables the data, any real reason for it or does not have something better to do with it?! What do you really need to do with the data? It may be that a select * from table order by Rand() can solve, then in loop you can separate, will already come in random order the same data.

  • When you have any questions regarding the use of a certain command in SQL Server, just click F1 on the command that will open the SQL Server helper, where you have examples of how to use and explain every command.

3 answers

1

I think that’s what you expect.

DECLARE @nQtdeGrupo INT = 5

SELECT cNome
     , iGrupo 
  FROM (SELECT cNome  = PESSOAS.NOME
             , iGrupo = (ROW_NUMBER() OVER(ORDER BY NEWID() DESC) % @nQtdeGrupo) + 1
          FROM PESSOAS
       ) tGrupo
 ORDER BY iGrupo

0

Using your logic you take the rest of the division by 5 that will stand between a number between 0 and 5.

SELECT *, FLOOR((RAND() * 1481) % 5) AS grupo FROM pessoas ORDER BY grupo

Because the RAND function returns a number between 0 and 1 it is necessary to multiply to achieve a large number integer.

Code

  • If I use a sequential Id attribute I would try to make the MOD for it to be better distributed, using RAND one group can be more unbalanced than another.

  • Heat, your select is giving conversion error. As the RAND generates a float, you need to convert it to int, before splitting, something like this: SELECT FLOOR(CAST((RAND() * 1481) AS int) % 5)

0

You can split using the NTILE function, example:

DECLARE @Count int
       ,@MaxRowsPerTable int = 5

SELECT @Count = COUNT() FROM dbo.Pessoas

/* Número máximo de tabelas que serão criadas */
SET @MaxCreationTable = FLOOR(@Count / @MaxRowsPerTable)

SELECT NTILE(@MaxCreationTable) OVER (ORDER BY Nome) 'Grupo', Nome FROM Pessoas

I mention how many rows you will have per table, then calculate the maximum number of tables that will be created and play this value within the NTILE parameter. This allows you to create an almost perfect balance (perfect only if the number of rows is divisible by the total number of rows in a table.

From there, you can create a WHILE responsible for creating dynamic tables based on Count and inserting values where COUNT is equal to the Group of NTILE

See if this meets your need.

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.