CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

TSQL Random Select with Selective Criteria

My database has 5 categories in table "category". I also have a table called "items", where each item has unique Id and a category Id FK.

I need to randomly select 10 items from 1 category.

This would not be problem if there was only 1 category. But table "items" stores categories id in non-sequential order.

The random select statement below works and is able to generate random IDs within a range. But how can I generate 10 random IDs that belong to the same category? 

Declare @maxRandomValue tinyint = 100
    , @minRandomValue tinyint = 0;

Select Cast(((@maxRandomValue + 1) - @minRandomValue) 
    * Rand() + @minRandomValue As tinyint) As 'randomNumber';

Defintions:

Table Categories
ID INT
Desc Varchar(100)

Table Items
ID Int
CategoryID Int (fk)
Desc Varchar(100)

Upvotes: 2

Views: 691

Answers (2)

gbn
gbn

Reputation: 432421

Use

  • a WHERE to filter to a category
  • NEWID to randomise rows
  • TOP to limit you to 10 items

So:

SELECT TOP 10
   *
FROM
   Items
WHERE
   CategoryID = @whatever
ORDER BY
   NEWID()

Upvotes: 6

t-clausen.dk
t-clausen.dk

Reputation: 44336

select top 10 * from items where categoryid = 1 order by newid()

Upvotes: 5

Related Questions