CODEWITHSUNDEEP
sql-serverrandom
Chris Cudmore
Chris Cudmore

Reputation: 30151

How do I select random rows from a database -- With a twist?

Assume (highly anonymized):

Create Table myTable
(
ID INT PK,
INDEXNUMBER INT,
VERSION INT,
Data VARCHAR(MAX)
)

This table is used to store mutually exclusive data. For example:

100 1 1 BOB
217 1 2 JOHN
319 1 3 GEORGE
420 7 1 MARY
415 7 2 SUSAN

In this case, I need to randomly pick ONE of BOB, JOHN or GEORGE and ONE of MARY or SUSAN.

I'm happy with either the ID or the INDEXNUMBER/VERSION pair.

If it helps to think about it, it's like picking a single shift of a hockey team from a table containing a roster:

Pick 1 Center from 3 available, Pick 1 Left Wing from 5 available, etc.

I've been playing with NEWID() and MAX/MIN (Cast NEWID to varchar first) but I keep getting hung up on the GROUP BY. If I GROUP BY ID, then max is operating on a single row at a time, yielding the entire table.

If I GROUP BY INDEXNUMBER, VERSION I get a similar result (The pair being unique).

What I need to do is GROUP BY INDEXNUMBER (excluding ID from the query entirely) yet somehow retrieve the VERSION.

Upvotes: 4

Views: 196

Answers (2)

Gregor Primar
Gregor Primar

Reputation: 6805

Solution is simple, join data with temp table. Please see my sample code:

--drop temp table if exists...
if OBJECT_ID('tempdb..#tempData') is not null
begin
 drop table #tempData
end
go

--select distinct INDEXNUMBER and fill temp table...
select distinct INDEXNUMBER into #tempData
from myTable
go

--join temp table with actual data to get randon rows...
select T.INDEXNUMBER,
(
  select top 1 VERSION 
  from myTable where 
  INDEXNUMBER=T.INDEXNUMBER 
  order by NEWID()
) 'Version'
from #tempData T

This syntax is suitable for SQL Server. Happy coding!

Upvotes: 0

Matt Smucker
Matt Smucker

Reputation: 5234

Partition by the INDEXNUMBER (I'm assuming that you need one from each though it's not specifically stated) and order by NEWID()

SELECT ID
FROM (
    SELECT 
        Row_Number() OVER (PARTITION BY INDEXNUMBER ORDER BY NEWID()) Sort,
        ID, 
        Data
    FROM myTable
) s
WHERE Sort = 1

Sql Fiddle

Upvotes: 4

Related Questions