CODEWITHSUNDEEP
sql-server
Yury Scherbakov
Yury Scherbakov

Reputation: 260

SQL Server : select random rows of each type

I have following table:

id | customer | type
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1
 5 | Bob      | 5
 6 | Clark    | 5
 7 | Daniel   | 1
 8 | Mike     | 3

and following data:

count | type
------+------
2     | 5
1     | 1
1     | 3

or just array:

(5, 5, 1, 3)

I need select 2 random unique customers of type 5, 1 customer of type 1 and 1 customer of type 3 in one request

Upvotes: 1

Views: 90

Answers (1)

vercelli
vercelli

Reputation: 4767

This is like juergen's approach but it'll work with any configuration of types.

select t1.id
  from 
       (
        select c.id, 
               t.count,
               row_number () over (partition by t.type order by newid()) as rn
           from customerTable c join typeTable t on c.type = t.type) as t1
where t1.rn <= t1.count;

Upvotes: 3

Related Questions