CODEWITHSUNDEEP
sqlsql-serverrandomnewid
Abbaskhan
Abbaskhan

Reputation: 87

SQL select random record

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

Upvotes: 0

Views: 531

Answers (3)

Gareth Lyons
Gareth Lyons

Reputation: 1972

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

Upvotes: 1

Higgs
Higgs

Reputation: 622

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

Upvotes: 0

jhilden
jhilden

Reputation: 12429

SELECT DISTINCT name
from tbl
ORDER BY NEWID()

Upvotes: 0

Related Questions