Search code examples
sqlsql-serverrandomgenerate

Generate random records from the table tblFruit based on the field Type

I will need your help to generate random records from the table tblFruit based on the field Type (without no duplication)

Table: tblFruit

As per the above table.

There are 4 type of fruit number 1,2,3,4

I want to generate x records dynamically from the table tblFruit (e.g 7 records). Let say I need to get 7 random record of fruit .

My result should contains fruit of the different types. However, we need to ensure that the result contains only 7 records.

i.e 2 records of type 1, 2 records of type 2, 2 records of type 3, 1 records of type 4

e.g

My Result

Note: If i want to generate 10 records (without no duplication), then i will get 2 records of each type and the two remaining records randomly of any type.

Much grateful for your help.

Solution

  • I might suggest:

    select top (7) f.*
from tblfruit f
order by row_number() over (partition by type order by newid());

    This will actually produce a result with approximately the same number of rows of each type (well, off by 1), but that meets your needs.