Randomly Select Record From Join Result

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Randomly Select Record From Join Result

Randomly Select Record From Join Result

Rate Topic

Display Mode

Topic Options

Author

Message

Gurpreet Atwal

Posted Wednesday, October 14, 2009 1:12 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 69, Visits: 59

Hi All,
Here is what I am trying to do.

I am joining two tables the records in table 1 can match multiple records in table two.

I need to randomly select one record from the resultset.

The query below returns 4 rows for each account id along with a Random identifier.

How can I select one record from the result set for each record in a RANDOM order ie the final resultset will have two records one for account id 1 and one for accountid 2.

Thanks
Guru

DROP TABLE #a
GO

SELECT AccountID, NewAccountId , NewId() Random
into #a
FROM
( select 1 AccountId,2 NewAccountId
UNION ALL select 1,3
UNION ALL select 1,4
UNION ALL select 1,5
UNION ALL select 2,6
UNION ALL select 2,7
UNION ALL select 2,8
UNION ALL select 2,9
)a

select * from #a

Post #803048

The Dixie Flatline

Posted Wednesday, October 14, 2009 2:43 PM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:02 PM
Points: 3,788, Visits: 5,543

This will pick random rows from your table without regard to the sequence of the [NewAccountID] or [Random] columns.

;with cte as (select row_number() over(partition by accountid order by newid()) as rowID,* from #a)
select * from cte where rowID = 1

This generally performs well with sets of rows numbering up to tens of thousands.

If the NewAccountIDs are not interleaved with AccountID, you could also get min/max values of NewAccountIds and randomly pick a row from each range.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #803107

ChiragNS

Posted Wednesday, October 14, 2009 11:49 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

SELECT TOP 1 AccountID, NewAccountId from #a
ORDER BY CASE WHEN CAST((RAND() * 10) AS INT)%2 = 0 THEN AccountID ELSE NewAccountId END ASC

Using newid() method is more "random" though.

"Keep Trying"

Post #803241

« Prev Topic | Next Topic »

Permissions