SQL - Draw random lines

Question

Given the table as an example:

I want to draw, between the lines with the same order number, a single line, that is, one of order 1, one of order 2 and one of order 3, randomly. I need a generic solution, which applies to a table with n orders.

Answer 1

Try it like this:

SELECT t.ordem,
       (SELECT TOP 1 rodada FROM tabela WHERE ordem = t.ordem ORDER BY NEWID())
FROM (SELECT DISTINCT ordem FROM tabela) t

See in SQL Fiddle

The call to the NEWID() within the ORDER BY generates a GUID for each row returned. It’s not exactly random, but it’s generic and simple enough, since SQL Server doesn’t have any specific function to generate a random number.

Answer 2

Can be done with ROW_NUMBER() as well...

SELECT ORDEM, RODADA
FROM 
(
    SELECT ORDEM, RODADA, ROW_NUMBER() OVER(PARTITION BY ORDEM ORDER BY NEWID()) AS ROWORDER
    FROM #TMP_RODADAS
) TEMP
WHERE 
    ROWORDER = 1

Answer 3

One solution is to use a subquery with ROW_NUMBER using PARTITION BY by the column you want to be the grouping and sorting by NEW_ID which will ensure randomisation:

SELECT x.*
  FROM (SELECT ROW_NUMBER() OVER(PARTITION BY t.ordem ORDER BY NEWID()) AS sequencia,
               t.*
          FROM tabela t) x
 WHERE sequencia <= 1

---

In the query above:

• We used the ROW_NUMBER to obtain the return of the groupings that will be used outside the subquery;
• We performed the PARTITION BY by the column to be used for grouping;
• We order by NEW_ID which will ensure that the sequence will be random;
• We use the resulting column (in this case sequencia) to limit the number of records we want per grouping (in the example it would be only 1 per group);

---

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

---

NEWID

Creates a Unique value of type uniqueidentifier.

In free translation:

Creates a unique value of type uniqueidentifier;

Answer 4

taking advantage of Vitor’s idea for the order by newid(), because it seems to me that Rand() did not work.

select distinct 
    t.ordem,
    (select top 1 
         x.rodada 
     from temp x 
     where x.ordem = t.ordem 
     order by newid()) as rodada
from temp t

see on Sqlfiddle: http://sqlfiddle.com/#! 6/a95f7/2