0
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
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.
Why do you need this "t" at the end of the query?
– Francisco
This "t" is what we call the "alias". It’s basically the name I gave to the bottom sub-select
(SELECT DISTINCT...)
. In this case, I used this alias to differentiate the two sub-selects. Otherwise, in the WHERE clause of the sub-select from above(WHERE ordem = t.ordem)
, SQL wouldn’t know to whichordem
I was referring to myself. I hope you can understand...– Vítor Martins
Thanks @Vítormartins!!!
– Mr. Mister