I have a table like below and wish to select distinct people e.g row 2, 9, 11, 20. I don't want to select MAX() as that's not random. And I don't want to select Jack twice. It needs to be one person from each set of records
ID Name Category Level
1 Jack Dragon 3
2 Jack Falls 5
3 Jack Spider 5
4 Jack Apprentice 1
5 Jack Jolly 5
6 Luke Dragon 1
7 Luke Falls 1
8 Luke Spider 3
9 Luke Apprentice 5
10 Luke Jolly 5
11 Mark Dragon 3
12 Mark Falls 3
13 Mark Spider 1
14 Mark Apprentice 3
15 Mark Jolly 1
16 Sam Dragon 3
17 Sam Falls 5
18 Sam Spider 5
19 Sam Apprentice 5
20 Sam Jolly 3
Assuming set of records = rows with the same value of "Name":
with cte_random
as
(
select *, rank() over (partition by forenames order by newid()) as rnk from tbl
)
select id, name, category, level from cte_random where rnk = 1