Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Randomly Select Record From Join Result Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 7:09 AM
Points: 102, 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
Posted Wednesday, October 14, 2009 2:43 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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
Posted Wednesday, October 14, 2009 11:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:55 AM
Points: 2,366, Visits: 1,845
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 »

Add to briefcase

Permissions Expand / Collapse