October 14, 2009 at 1:12 pm
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
October 14, 2009 at 2:43 pm
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? Everybody look what's going down. -- Stephen Stills
October 14, 2009 at 11:49 pm
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"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply