CODEWITHSUNDEEP
sqlsql-server
Kaan Baris
Kaan Baris

Reputation: 3401

SQL Server Random Rows for each column type

i am trying to get 3 different players for thier playerRole column . I am using

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 1 ORDER BY NEWID()

query to get random row for playerRole = 1

However i want to get 

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 1 ORDER BY NEWID()

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 2 ORDER BY NEWID()

SELECT TOP 1 * FROM FantasyPlayers WHERE playerRole = 3 ORDER BY NEWID()

3 random players with 3 different role which means one 1 random player for each playerRole .

I tried Union which is not returning anything due to NEWID()

Any help is appreciated

Thanks

Upvotes: 1

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can also do this without a subquery:

select top (1) with ties fp.*
from FantasyPlayers fp
order by row_number() over (partition by playerRole order by newid();

Upvotes: 2

Thom A
Thom A

Reputation: 95830

Perhaps...

WITH CTE AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY playerRole
                              ORDER BY NEWID()) AS RN
    FROM FantasyPlayers)
SELECT *
FROM CTE
WHERE RN = 1;

Upvotes: 2

Related Questions