SQL - Sorting Random Lines

You can do with ROW_NUMBER () too ...

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

Try 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 NEWID() within ORDER BY generates a GUID for each row returned. It is not exactly random, but it is generic and simple enough, since SQL Server has no specific function to generate a random number.

One solution is to use subquery with ROW_NUMBER using PARTITION BY for the column you want to be grouping and sorting by NEW_ID that will guarantee randomization:

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% with% above:

• We use query to get the returns of clusters that will be used outside ROW_NUMBER ;
• We perform subquery by the column that will be used to group;
• We ordered by PARTITION BY that will ensure that the sequence will be random;
• We used the resulting column (in the NEW_ID case) to limit the number of records we want by grouping (in the example it would only be 1 per group);

  

sequencia

     

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

  

ROW_NUMBER

     

Creates a unique value of type NEWID .

Free translation:

  

Create a unique value of type uniqueidentifier ;

taking advantage of colleague Vitor's idea for 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

look at SQLFiddle: link