SQL select random record

Question

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

What do you mean exactly when you say "distinct people"? That the Name must be unique? Or the combination of Name and Category? If you choose ID=2 (Jack, Falls), can you select 7 (Luke, Falls) then? In your example (2, 9, 11, 20) every Name is unique and every Category is also unique, so it is not clear whether that is a requirement or not. On the other hand I guess the Level doesn't matter, because in your example 2 and 9 have Level = 5 and 11 and 20 have Level = 3. — Fabio says Reinstate Monica, Mar 10 '16 at 17:18

score 1 · Accepted Answer · answered Mar 10 '16 at 18:42

1

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

answered Mar 10 '16 at 18:42

Gareth Lyons

1,942
12
14

score 0 · Answer 2 · answered Mar 10 '16 at 16:56

0

SELECT DISTINCT name
from tbl
ORDER BY NEWID()

answered Mar 10 '16 at 16:56

jhilden

12,207
5
53
76

This will select just the name field? I want the entire record. result should return 4 lines – Abbaskhan Mar 10 '16 at 17:01

score 0 · Answer 3 · answered Mar 10 '16 at 18:35

This seems trickier than it sounds, creating a temp table with an extra tempId column should work. Try:

create table #temp(ID int, Name char(10), Category char(10), Level int, tempId varchar(36))

insert #temp select ID, Name, Category, Level, NEWID() as 'tempId' from yourTable
select ID, Name, Category, Level from #temp where ID IN
    (select min(tempId) from #temp group by Name)

drop table #temp

SQL select random record

3 Answers3